Type of Constraint in SQL Server?

Step 1 : - Type of Constraint in SQL Server

create table PrimaryData

(

       RollNo varchar(50) constraint pk_Prim primary key,

       StudentName varchar(50) not null ,

       StudentMob varchar(10) constraint un_prim unique(StudentMob),

       StudentHomNo varchar(10) null,

       StudentAge int constraint ck_prim check(StudentAge between 3 and 25),

       InsDtTime datetime default(getdate())

)

Create table ForeignData

(

       RollNo varchar(50) constraint fk_fore references PrimaryData(RollNo),

       Standard varchar(10) not null,

       Address varchar(250) null

)

Note : Look into the respective column of PrimaryData and ForeignData table where I have used different type of constraint and below is the details...

1. PRIMARY KEY (Trying to insert null value)

insert into PrimaryData(RollNo,StudentName,StudentMob,StudentHomNo,StudentAge)

values(NULL,'Deepak Panchal','9876543210','28888888','3')

--The statement has been terminated. Error :

--Cannot insert the value NULL into column 'RollNo', table 'PrimaryData'; column does not allow nulls.

--Causes

--1. RollNo column does not accept 'NULL' value.

--2. RollNo column is assign 'PRIMARY KEY' constraint.

insert into PrimaryData(RollNo,StudentName,StudentMob,StudentHomNo,StudentAge)

values('1','Deepak Panchal','9876543210','28888888','3')

--Record Insert Successfully

2. NOT NULL

insert into PrimaryData(RollNo,StudentMob,StudentHomNo,StudentAge)

values('2','NULL','29999999','3')

--Record INSERT fails Error:

--Cannot insert the value NULL into column 'StudentName', table 'PrimaryData'; column does not allow nulls

--Causes

--1. StudentName is asign 'not null' constraint that why its not accept null value

3. UNIQUE KEY 

insert into PrimaryData(RollNo,StudentName,StudentMob,StudentHomNo,StudentAge)

values('2','Rahul Morya','9876543210','29999999','3')

--The statement has been terminated. Error :

--Violation of UNIQUE KEY constraint. Cannot insert duplicate key in object 'PrimaryData'.

--Causes

--1. StudentMob column '9876543210' already exits in the table

--2. StudentMob column is assign 'unique' constraint that why each record is unique in row

insert into PrimaryData(RollNo,StudentName,StudentMob,StudentHomNo,StudentAge)

values('2','Rahul Morya',NULL,'29999999','3')

--Record Insert Successfully

insert into PrimaryData(RollNo,StudentName,StudentMob,StudentHomNo,StudentAge)

values('3','Jaya Gala',NULL,'27777777','4')

--The statement has been terminated. Error :

--Violation of UNIQUE KEY constraint. Cannot insert duplicate key in object 'PrimaryData'.

--Causes

--1. StudentMob column 'NULL' already exits in the table

--2. UNIQUE KEY constraint accept only one 'NULL' value in StudentMob column.

insert into PrimaryData(RollNo,StudentName,StudentMob,StudentAge)

values('3','Jaya Gala','8866445566','4')

--Record Insert Successfully

4. CHECK 

insert into PrimaryData(RollNo,StudentName,StudentMob,StudentHomNo,StudentAge)

values('4','Sapna Desai','8898698699','32654455','26')

--The statement has been terminated. Error :

--INSERT statement conflicted with COLUMN CHECK constraint. The conflict occurred in database 'database name', table 'PrimaryData', column 'StudentAge'.

--Causes

--1. StudentAge column accept only age between 3 and 25.

--2. StudentAge column is assign 'CHECK' constraint.

insert into PrimaryData(RollNo,StudentName,StudentMob,StudentHomNo,StudentAge)

values('4','Sapna Desai','8898698699','32654455','5')

--Record Insert Successfully

5. PRIMARY KEY 

insert into PrimaryData(RollNo,StudentName,StudentMob,StudentHomNo,StudentAge)

values('4','Sujit Tyagi','8888888888','36565654','5')

--The statement has been terminated. Error :

--Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'PrimaryData'.

--Causes

--1. RollNo column accept only unique value that is identical in each row.

--2. RollNo column is assign 'PRIMARY KEY' constraint.

6. FOREIGN KEY

insert into ForeignData(RollNo,Standard,Address)

values('5','jr kg','Malad East')

--The statement has been terminated. Error :

--INSERT statement conflicted with COLUMN FOREIGN KEY constraint. The conflict occurred in database 'database name', table 'PrimaryData', column 'RollNo'.

--Causes

--1. RollNo column accept only thoes 'RollNo' that is in 'PrimaryData' table 'RollNo' column.

--2. RollNo column is assign 'FOREIGN KEY' constraint.

insert into ForeignData(RollNo,Standard,Address)

values('1','jr kg','Malad East')

--Record Insert Successfully

insert into ForeignData(RollNo,Standard,Address)

values('2','jr kg','Malad East')

--Record Insert Successfully

insert into ForeignData(RollNo,Standard,Address)

values('3','1st','Malad East')

--Record Insert Successfully

insert into ForeignData(RollNo,Standard,Address)

values('4','1st','Malad East')

--Record Insert Successfully

insert into ForeignData(RollNo,Standard,Address)

values('4','1st','Malad East')

--Record Insert Successfully

--Causes

--1. RollNo column accept duplicate record.

--2. RollNo column is assign'FOREIGN KEY' constraint.

Important Point : -

/*

truncate table PrimaryData

truncate table ForeignData

delete from PrimaryData

delete from ForeignData

--1. Primary key created table when reference to other table as foreign key, we can't truncate

PrimaryData table data but we can delete it. 

drop table PrimaryData

drop table ForeignData

--2. Primary key created table when reference to other table as foreign key, we can't drop

PrimaryData table until drop ForeignData table first.

*/