MySQL Column Modifiers
Modifiers can optionally be used to further control column content.
- NOT NULL
- UNIQUE
- AUTO_INCREMENT
- DEFAULT
- PRIMARY_KEY
NOT NULL
Insists that each record must include a data value in this column. This allows an empty string “” to be stored there but does not allow that column to be empty or NULL.
Ex :
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE IF NOT EXISTS tblemployees ( Empcode INT NOT NULL, Empname VARCHAR(100) NOT NULL ); |
UNIQUE
Insists that records may not duplicate any entry in this column. When the data stored in a column should never be duplicated that column should be defined with the UNIQUE modifier.
Ex :
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE IF NOT EXISTS tblemployees ( Empcode INT NOT NULL UNIQUE, Empname VARCHAR(100) NOT NULL ); |
AUTO_INCREMENT
Available only for numeric columns, to automatically generate a number that is one more than the previous value in that column.
The AUTO_INCREMENT modifier is particularly useful to automatically generate incremental identity numbers for each row – the first row will be numbered 1, the second row 2, and so on.
Ex:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE IF NOT EXISTS tblemployees ( id INT UNIQUE AUTO_INCREMENT, Empcode INT NOT NULL UNIQUE, Empname VARCHAR(100) NOT NULL ); |
DEFAULT
Specifies a value to be used where no value is stated for this column when a record is inserted. Setting a column DEFAULT allows records to be inserted without te-diously requiring a value for a column that is usually contant. For instance a “quantity” column might usually contain a value of 1 in each record – so 1 could be set as its default value.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE IF NOT EXISTS tblemployees ( id INT UNIQUE AUTO_INCREMENT, Empcode INT NOT NULL UNIQUE, Empname VARCHAR(100) NOT NULL, empCity varchar(255) DEFAULT 'New Delhi' ); |
PRIMARY KEY
A PRIMARY KEY is a constraint that is applied to a column to uniquely identify each row of that database table. It ensures that the values in each row of that column are unique and never change, so those values can be used to reference any specific row.
Each field that column must have a value -it may not be empty or have a NULL value.
Each value in that column must be unique – there must be no duplication.
Each value in that column can never be modified or updated.
Each value in that column in that column cannot be re-used – when a row is deleted its PRIMARY KEY value cannot be re-assigned as the PRIMARY KEY values of a new row.
Ex :
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE IF NOT EXISTS tblemp ( id INT AUTO_INCREMENT PRIMARY KEY, Empname VARCHAR(100) NOT NULL ); |
