SQL Constrains

Constrains are the limitations or rules. We are controlling the data using Constrains.

 

NOT NULL Constrains

We can create a table without null values using NOT NULL constrains.

Example:

 

CREATE TABLE EMPLOYEE(

EmpID INT NOT NULL,

EmpName varchar(255) NOT NULL,

EmpMail varchar(255) NOT NULL

);

PRIMARY KEY Constrains

• Primary key must contain unique values
• Primary Key cannot hold any null values
• Each column can have only one primary key
• Primary keys can contain single and multiple columns

Example:

CREATE TABLE EMPLOYEE(

EmpID INT NOT NULL PRIMARY KEY,

EmpName varchar(255) NOT NULL,

EmpMail varchar(255) NOT NULL

);

INSERT INTO EMPLOYEE VALUES(1,'TOM','tom@gmail.com');

INSERT INTO EMPLOYEE VALUES(1,'TOM','tom@gmail.com');

It will throw an error like:

Error: near line 8: UNIQUE constraint failed: EMPLOYEE.EmpID

Primary Key on Multiple columns

Example:

CREATE TABLE EMPLOYEE(

EmpID INT NOT NULL,

EmpName varchar(255) NOT NULL,

EmpMail varchar(255) NOT NULL,

CONSTRAINT PK_EMPLOYEE PRIMARY KEY(EmpID,EmpName)

);

INSERT INTO EMPLOYEE VALUES(1,'TOM','tom@gmail.com');

INSERT INTO EMPLOYEE VALUES(1,'TOMY','tom@gmail.com');

Select * from EMPLOYEE

When we give same values for ID and First name like

INSERT INTO EMPLOYEE VALUES(1,'TOM','tom@gmail.com');

INSERT INTO EMPLOYEE VALUES(1,'TOM','tom@gmail.com');

It will throw an error like:

Error: near line 11: UNIQUE constraint failed: EMPLOYEE.EmpID, EMPLOYEE.EmpName

Add primary key to an existing table

ALTER TABLE EMPLOYEE ADD PRIMARY KEY(EmpName);

Drop primary key of an existing table

ALTER TABLE EMPLOYEE DROP PRIMARY KEY;