SQL CONSTRAINT
SQL CONSTRAINT
• 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 a 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 for a column
ASSUME BELOW TABLE
Create Table Customer(
CID int, Cname varchar(20), Age int, Email varchar(30),SSN int,Address varchar(30));
Syntax for adding primary key constraint:
SQL PRIMARY KEY Constraint: alter table Table_Name
add constraint Constraint_Name primary key(Attribute)
To add primary key constraint in ‘CID’ Attribute:
alter table customer Error: Cannot define PRIMARY KEY constraint on nullable column in
add constraint pkid primary key(Cid) table 'customer'.
First make it NOT NULL attribute before adding primary key constraint
alter table customer alter column cid int not null
i n t:
Now, CID Attribute is NOT NULL st ra
n
y co
alter table customer y ke
mar er
add constraint pkid primary key(Cid) ri m
v ep usto pkid
o e c nt
e m l
b trai
R t a
To l ter cons
a p
dro
SQL UNIQUE CONSTRAINT
Syntax for adding Unique constraint:
alter table Table_Name
add constraint Constraint_Name Unique(Attribute)
To Add unique constraint on Email Attribute:
ALTER TABLE customer
ADD CONSTRAINT unimail UNIQUE (email)
To Remove unique constraint on Email Attribute:
ALTER TABLE customer
drop constraint unimail
FOREIGN KEY CONSTRAINT
Syntax for adding Foreign Key constraint:
alter table Table_Name
add constraint Constraint_Name Foreign Key(Attribute) references Ref_Table_Name(Attribute)
To Add Foreign Key constraint on SSN Attribute:
ALTER TABLE customer
add constraint fkssn foreign key(ssn) references publication(pubid)
To Remove Foreign Key constraint on SSN Attribute:
ALTER TABLE customer
drop constraint fkssn
NOT NULL CONSTRAINT
Syntax for adding Not Null constraint:
alter table Table_Name
Alter Column Attribute_Name Data type Not Null
To Change Not Null constraint on CNAME Attribute:
ALTER TABLE customer
alter column CName varchar(20) not null
To Remove NOT NULL constraint From CNAME Attribute:
ALTER TABLE customer
alter column CName varchar(20)
SQL DEFAULT CONSTRAINT
Syntax for adding Default constraint:
alter table Table_Name
add constraint Constraint_Name default default_value for Attribute
To Add Default constraint on Email Attribute:
ALTER TABLE customer
add constraint dfmail default 'info@info.com' for email
To Remove default constraint on Email Attribute:
ALTER TABLE customer
drop constraint dfmail
SQL CHECK CONSTRAINT
Syntax for adding Check constraint:
alter table Table_Name
add constraint Constraint_Name Check(Attribute Condition)
To Add Check constraint on Age Attribute:
ALTER TABLE customer
add constraint chkage check(age>=20 and age<=40)
To Add Check constraint on Address Attribute:
ALTER TABLE customer
add constraint CHKAdd check(Address='Dharan' or Address='Damak' or Address='Itahri')
To Remove Check constraint on Email Attribute:
ALTER TABLE customer
drop constraint CHKAdd
ADD NEW COLUMN IN ALREADY EXIST TABLE
To Add New Column on Customer Table:
ALTER TABLE customer
New Location Column added on Customer Table
add Location varchar(30)
Adding New Column with Constraint:
ALTER TABLE customer New Direction Column added with constrint on Customer
add Direction varchar(20) check(location='East') Table
To Rename Table Name:
Exec sp_rename customer,customers
