How-To MySQL CREATE TABLE Example showing default values, NULL values and comments

This example shows how to set default values for each column — as well as how to allow (or not allow) NULL values in the column. By default, any column can contain NULL values.

(Note that we’re also using “CREATE TABLE IF NOT EXISTS” so an error doesn’t get thrown if the table already exists when we try to create it.)

We’ve added comments as well, though we’ll explain those after.

/*
 Create the table foo.example_02 if it doesn't exist.
*/
CREATE TABLE IF NOT EXISTS `foo`.`example_02` (
 `field_1` VARCHAR(255) NOT NULL DEFAULT '',
 `field_2` CHAR(20) NOT NULL default 'default_text',
 `field_3` INT(10) DEFAULT NULL,
 `field_4` BIGINT(20),
 `field_5` DATETIME          -- embed a comment in the definition like this...
);

Note that if you don’t specify NULL or NOT NULL, columns by default can contain NULL values. And not specifying a default is the same as specifying ‘DEFAULT NULL’ since MySQL uses NULL as the default value in both cases.

Note also that I’ve added comments to the declaration. Comments come in two types — multi-line comments, and single-line comments.

/*
      This is a multi-line comment
*/

-- This is single-line comment 

select foo from bar;   -- This means the rest of this line is a comment

Click here to go to the table of contents for all MySQL Programming Example Code
This entry was posted in mysql and tagged , , . Bookmark the permalink.
  • Praveena

    What are the benefits of using either NULL or NOT NULLs?
    thanks

    Current score: 0
  • http://twitter.com/kbedell Kevin Bedell

    The benefit of having NULL or NOT NULL values comes from allowing the database to enforce constraints on the data to match your application.

    For example, if you have a CUSTOMER table that requires a particular ‘customer_type’ attribute, you can make the ‘customer_attribute’ column ‘NOT NULL’ — meaning the data cannot be saved into the database unless a value for that column is provided.

    Sometimes, however, having a value be NULL is fine — for example, if the same CUSTOMER table has both ‘work_phone’ and ‘home_phone’ columns. Maybe they aren’t both required — so those values can be allowed to be NULL.

    I hope this answers your questions – thanks for reading!

    Current score: 0
  • http://www.iddaalive.com/ canlı maç izle

    thanks this is very helpful

    Current score: 0
  • Arunstalins

    thakqqqqqqqqq

    Current score: 0
  • Mohamed

     mmmm

    Current score: 0
  • Anonymous

    how to add default value with alter table?

    Current score: 0
  • VCR

    Gfssbn

    Current score: 0
  • prasant adhikari

    nice!

    Current score: 0