PHPGurukul

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 :

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 :

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: 

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.

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 :

CREATE TABLE IF NOT EXISTS tblemp
(
id
  INT AUTO_INCREMENT
      PRIMARY KEY,
Empname
VARCHAR(100)
NOT NULL
);

Anuj Kumar

This is Anuj Kumar. I’m a professional web developer with 4+ year experience. I write blogs in my free time. I love to learn new technologies and share with others.
I founded PHPGurukul in September 2015. The main aim of this website to provide PHP, Jquery, MySQL, PHP Oops and other web development tutorials.
.

Recommended Tutorials for you


Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.