Constraint in sql example or constraint in sql Or SQL Constraints

Step 1:- Create PrimaryData table

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())

)

Step 2:- Create ForeignData table

Create table ForeignData

(

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

Standard varchar(10) not null,

Address varchar(250) null

)

/* PRIMARY KEY */

a.  The PRIMARY KEY constraint uniquely identifies each record in a database table.

b.  A primary key column cannot contain NULL values.

c.  Table can have only ONE primary key

Step 1:- Insert NULL value in Primary Key column

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

Values (NULL, ‘Deepak Panchal','9876543210','28888888','3')

Causes

1.  ‘RollNo’ column does not accept 'NULL' value.

2.  ‘RollNo’ column is assign ‘PRIMARY KEY' constraint.

Step 2:- Insert Record

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

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

Record Insert Successfully

/* NOT NULL */

a.  The NOT NULL constraint enforces a column to NOT accept NULL values.

b.  The NOT NULL constraint enforces a field to always contain a value.

Step 1:- Insert NULL value in NOT NULL column

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

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

Causes

1.  StudentName is assign 'not null' constraint that why it’s not accept null value

/* UNIQUE KEY */

a.  The UNIQUE constraint uniquely identifies each record in a table.

b.  A UNIQUE constraint column at least contain one NULL values.

c.  Table can have many UNIQUE constraints per table.

Step 1:- Insert NULL value in NOT NULL column

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

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

Causes

1.  ‘StudentMob’ column '9876543210' already exist in the table

2.  ‘StudentMob’ column is assign ‘unique’ constraint that why each record is unique in each row.

Step 2:- Insert NULL value in UNIQUE KEY Column

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

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

Record Insert Successfully

Step 3:- Again Insert NULL value in UNIQUE KEY Column

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

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

Causes

1.  ‘StudentMob’ column 'NULL' already exist in the table

2.  UNIQUE KEY constraint accepts only one 'NULL' value in ‘StudentMob’ column.

Step 4:- Insert Record

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

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

Record Insert Successfully

/* CHECK */

a.  The CHECK constraint is used to limit the value range that can be placed in a column.

b.  If you define a CHECK constraint on a single column it allows only certain values for this column.

Step 1:- Insert Student Age Not between 3 and 25

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

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

Causes

1.  ‘StudentAge’ column accept only age between 3 and 25.

2.  ‘StudentAge’ column is assign 'CHECK' constraint.

Step 2:- Insert Record

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

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

Record Insert Successfully

/* PRIMARY KEY */

Step 1:- Insert Duplicate record in Primary key column

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

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

Causes

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

2.  ‘RollNo’ column is assign ‘PRIMARY KEY' constraint.

/* FOREIGN KEY */

Step 1:- Insert record in ForeignData table which is not in PrimaryData table

Insert into ForeignData (RollNo,Standard,Address)

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

Causes

1.  ‘RollNo’ column accept only those 'RollNo' that is in 'PrimaryData' table 'RollNo' column.

2.  'RollNo’ column is assign ‘FOREIGN KEY' constraint.

Step 2:- Insert Record

Insert into ForeignData (RollNo,Standard,Address)

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

Record Insert Successfully

Step 3:- Insert Record

Insert into ForeignData (RollNo,Standard,Address)

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

Record Insert Successfully

Step 4:- Insert Record

Insert into ForeignData (RollNo,Standard,Address)

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

Record Insert Successfully

Step 5:- Insert Record

Insert into ForeignData (RollNo,Standard,Address)

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

Record Insert Successfully

Note:-

In ForeignData table you can insert a duplicate value but that value must exists in PrimaryData table