Column Options When Creating Tables

MySQL Tutorials - Herong's Tutorial Examples

∟Column Options When Creating Tables

A tutorial example is provided on how to use different column options like, NULL, UNIQUE, DEFAULT, PRIMARY KEY, when creating new tables.

Table columns can be created with various options. Some of them are:

1. "NOT NULL" or "NULL" - Indicating whether or not null value is allowed in this column. The default is "NULL".

2. "DEFAULT default_value" - Providing a default value to this column, in case there is no value specified for this column when creating a new record.

3. "PRIMARY KEY" - Indicating that this column is a primary key, which requires non-null and unique values. Only one column can be defined as a primary key per table.

4. "UNIQUE" - Indicating that this column must take unique values or null values.

5. "AUTO_INCREMENT" - Indicating that this column will automatically take the next value of a sequence. The sequence starts with 0, and incremented by 1. Auto increment can only be specified on a primary key column.

Below is a sample SQL code, ColumnOptions.sql, showing you different column options:

-- ColumnOptions.sql
-- Copyright (c) 1999 HerongYang.com. All Rights Reserved.
--
CREATE TABLE IF NOT EXISTS User (ID INT PRIMARY KEY AUTO_INCREMENT,
   Login CHAR(8) NOT NULL, Password CHAR(8) UNIQUE,
   Email CHAR(32) DEFAULT 'info@hy.com');
INSERT INTO User VALUES (null,'herong','8IS3KOXW','herong@hy.com');
INSERT INTO User (Login, Password, Email)
   VALUES ('mike','PZ0JG',null);
INSERT INTO User (Login, Password)
   VALUES ('mike','GZDN');
SHOW COLUMNS FROM User;
SELECT * FROM User;
DROP TABLE User;

If you run the code, you will get:

Field    Type    Null    Key     Default Extra
ID       int(11)         PRI     NULL    auto_increment
Login    char(8)
Password char(8)         YES     MUL     NULL
Email    char(32)        YES             info@hy.com
ID      Login   Password        Email
1       herong  8IS3KOXW        herong@hy.com
2       mike    PZ0JG           NULL
3       mike    GZDN            info@hy.com

As you can see from the output:

The AUTO_INCREMENT column works when you specify a null value or not specify any value to it.
The column DEFAULT value will not be used, if you specify a null value to the column.