SQL: 02 - Constraints

CONSTRAINTS

In SQL, we have the following constraints:

• NOT NULL - Indicates that a column cannot store NULL value

• UNIQUE - Ensures that each row for a column must have a unique value

• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly

• FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table

• CHECK - Ensures that the value in a column meets a specific condition

• DEFAULT - Specifies a default value when specified none for this column

 

Syntax(MySQL)

CREATE TABLE table_name

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

)

 

ADD CONSTRAINT

Syntax

ALTER TABLE table_name

ADD UNIQUE (column_name)

 

DROP CONSTRAINT

Syntax

DROP UNIQUE

DROP PRIMARY KEY

 

CHECK

CHECK (col_name

 

NOTNULL

During Table Creation

 

CREATE TABLE table_name

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

UNIQUE (P_Id)

)

 

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

)