Primary key constraint , foreign key constraint, unique key constraint, not null, check constraint in SQL Server

Description:

A Constraint is a property that we can assign to column in table. By assigning constraint property on column we can prevent the users to enter inconsistent of data in columns. We can assign these Constraint properties during the time of creation of table (By Using CREATE TABLE statement) or during the time of changing the existing table structure (By Using ALTER TABLE statement).

In SQL we have different types of constraints are available those are

1.      Primary Key Constraint

2.      Unique Key Constraint

3.      Foreign Key Constraint

4.      Not Null Constraint

5.      Check Constraint

Now I will explain about each constraint clearly

SQL Primary Key Constraint:

Primary key constraint is used to uniquely identify each record in database table. It won’t allow repetition or duplication of data. Each table is having only one primary key constraint and it contains only unique values. Primary key constraint doesn’t accept null values.

Example of creating Primary Key constraint during the time of CREATE TABLE

Create Table SampleUserDetail ( UserID integer PRIMARY KEY, UserName varchar(50), FirstName varchar(50), LastName varchar(50) )

Example of creating Primary Key constraint during the time of ALTER TABLE

ALTER TABLE SampleUserDetail ADD PRIMARY KEY (UserID)

To Drop Primary Key constraint on table use the below statement

ALTER TABLE SampleUserDetail DROP Constraint UserID

SQL Unique Key Constraint:

Unique key constraint is same as Primary key Constraint it doesn’t allow duplication or repetition of data in column and we can uniquely identify records in table. The main difference is Primary Key constraint won’t allow null values but unique key constraint allows null values. We have a chance to define only one primary key on table but we can define many unique key constraints on table.

Example of creating Unique Key constraint during the time of table creation

Create Table SampleUserDetail ( UserID integer , UserName varchar(50), FirstName varchar(50), LastName varchar(50) CONSTRAINT us_UserId UNIQUE (UserID) )

Example of creating Unique Key constraint during the time of ALTER TABLE

ALTER TABLE SampleUserDetail ADD CONSTRAINT us_UserId UNIQUE (UserID)

To Drop Unique Key constraint on table use the below statement

ALTER TABLE SampleUserDetail DROP Constraint us_UserId

SQL Foreign Key Constraint:

A Foreign key in one table point to primary key in another table. The foreign key constraint is used to prevent the actions that would destroy the links between two tables.

Example of Foreign key constraint

Create one table with primary key and give name as UserDetails

UserID	UserName	FirstName	LastName
1	SureshDasari	Suresh	Dasari
2	PrasanthiDonthi	Prasanthi	Donthi
3	MaheshDasari	Mahesh	Dasari

After create another table with Foreign Key and give name as SalaryDetails

SalID	Salary	UserID
1	10000	1
2	20000	2
3	30000	3

The column “UserID” is a primary key in UserDetails table

The column “SalID” is a foreign key in SalaryDetails tables

If you observe above two tables UserID in “UserDetails” table points to UserID in “SalaryDetails”

Example of creating Foreign Key constraint during the time of table creation

Create Table SalaryDetails ( SalaryID integer , Salary integer, UserID varchar(50), PRIMARY KEY (SalaryID), CONSTRAINT fk_SalaryID FOREIGN KEY(UserID) REFERENCES UserDetails(UserID) )

Example of creating Foreign Key constraint during the time of ALTER TABLE

ALTER TABLE SalaryDetails ADD CONSTRAINT fk_SalaryID FOREIGN KEY (UserID) REFERENCESUserDetails(UserID)

To Drop Foreign Key constraint on table use the below statement

ALTER TABLE SampleUserDetail DROP Constraint fk_SalaryID

SQL Not NULL Constraint:

If we set Not Null constraint property on any column in table that column won’t accept NULL or Empty values. If you want enforce any column not to accept NULL or empty value just set Not NULL Constraint property for that particular column

Example of creating NOT NULL constraint during the time of table creation

Create Table SampleUserDetail ( UserID integer NOT NULL, UserName varchar(50) NOT NULL, FirstName varchar(50), LastName varchar(50) )

SQL Check Constraint:

The check constraint is used to limit the value range that can be placed in a column. If we set Check constraint property on particular column the values in particular column must satisfy condition set by check constraint.   

Example of creating Check constraint during the time of CREATE TABLE

Create Table SampleUserDetail ( UserID integer NOT NULL CHECK(UserID > 0), UserName varchar(50), FirstName varchar(50), LastName varchar(50) )

Example of creating Check constraint during the time of ALTER TABLE

ALTER TABLE SampleUserDetails ADD CONSTRAINT chk_UserID CHECK(UserID > 0)

To Drop Check constraint on table use the below statement

ALTER TABLE SampleUserDetails DROP CONSTRAINT chk_UserID