Scribd
Upload
54 views

Practical SQL Constraint

The document discusses various SQL constraints - NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT. It provides the syntax for adding, altering, and removing each constraint on a sample CUSTOMER table. It also shows how to add a new column to an existing table and rename a table.

Uploaded by

rojina raut
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
54 views

Practical SQL Constraint

The document discusses various SQL constraints - NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT. It provides the syntax for adding, altering, and removing each constraint on a sample CUSTOMER table. It also shows how to add a new column to an existing table and rename a table.

Uploaded by

rojina raut
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 9

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

You might also like