Mysql

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
);


Recommended Tutorials for you


Related posts

MySQL Insert Into

Anuj Kumar

MySQL Database and Tables

Anuj Kumar

MySQL Introduction

Anuj Kumar

MySQL Altering tables

Anuj Kumar

MySQL Select Query

Anuj Kumar

How to Connect PHP with MySQL Database

Anuj Kumar

MySQLi Procedural Functions

Anuj Kumar

MySQL Command-line

Anuj Kumar

MySQL Users

Anuj Kumar

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

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