Column Options

A table must have at least one column. Each column has to have a name and a data type. And there are several options for column data that can be used as part of the table creation command. These options modify how MySQL handles the associated column. Such  as the following;

NULL 

•  Allows values stored as  NULL 
•  The default option

NOT NULL

• Disallows NULL  values 
• Saves response time and in some cases disk space 
• A necessity for a  PRIMARY KEY

DEFAULT
 

•  If no value is specified by the user at insertion of new data, this data will be stored in the field AUTO_INCREMENT 
• Only works for Integer data type columns
• Automatically generates a unique, positive number sequence for each row in a table (for UNIQUE or PRIMARY KEY  columns) 
• Using NULL will insert the next available number instead  
• Not specifying a value for the column will also work 
• Cannot exceed the upper limit of the  specific data type (i.e. 255 for a  TINYINT)   If rows are deleted containing values at the high end of a sequence, those values are not reused (for specific storage engine tables)

KEYS
 

• A  primary key is a clause assigned to a column that uniquely identifies a single row in the table. 
• A primary key cannot contain NULL values. 
• A  foreign key  is a column in a table that referen ces 
• A  unique key  is similar to a primary key, except that it can be allowed to contain  NULL values.
• Each non-NULL value uniquely identifies a single row in the table. 

Example:
               CREATE TABLE CountryLanguage` (
              `CountryCode` char(3) NOT NULL,
               `Language` char(30) NOT NULL,
               `IsOfficial` enum('T','F') NOT NULL DEFAULT 'False',
               `Percentage` float(4,1) NOT NULL DEFAULT '0.0',
                PRIMARY KEY  (`CountryCode`,`Language`)
                );