Data Definition Statements

Part:   1  2   3 

(Continued from previous part...)

Table Column Options

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 by Dr. Herong Yang
--
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.comm');
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.comm
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.

Creating Indexes

There are two ways to create indexes on a table:

1. Creating indexes using the CREATE TABLE statement:

CREATE TABLE tbl_name (col_name col_type col_options, ...,
INDEX idx_name (idx_column, ...),...);

2. Creating indexes using the CREATE INDEX statement:

CREATE INDEX idx_name ON tbl_name (idx_column,...);

To show the indexes of an existing table, you can use the show index statement:

SHOW INDEX FROM tbl_name;

Here is an example code on creating indexes:

-- CreateIndex.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
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',
   INDEX LoginIndex (Login));
INSERT INTO User VALUES (null,'herong','8IS3KOXW','herong@hy.comm');
INSERT INTO User (Login, Password, Email) 
   VALUES ('mike','PZ0JG',null);
INSERT INTO User (Login, Password) 
   VALUES ('mike','GZDN');
SELECT 'First show index';
SHOW INDEX FROM User;
CREATE INDEX EmailIndex ON User (Email);
SELECT 'Second show index';
SHOW INDEX FROM User;
DROP TABLE User;

(Continued on next part...)

Part:   1  2   3