Articles → SQL SERVER → Constraints In SQL Server

Constraints In SQL Server

A constraint is a restriction applied to a column so that the inconsistent data is not added to the column.

Primary Key Constraint

A primary key column: -

contains unique value
It should not be null

When we apply primary key constraint to any column, both the rules mentioned above is applied to the column.

Example

Consider the following code: -

Create table Test (
  PK_ID int, 
  sName varchar(100), 
  Primary key (PK_ID)
)

In this example, we are creating a table "Test" and applied Primary key constraint to the column "PK_ID". Once the constraint is applied, write the following insert statement to insert value in the "Test" table.

insert into Test values(1,'hi')

Write another insert statement to insert the duplicate value in the "PK_ID" column.

insert into Test values(1,'hi')

As the value is duplicate and the primary key constraint is applied, you will get the following error on the execution of the above insert statement.

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK__Test__078C1F06'. Cannot insert duplicate key in object 'dbo.Test'.

The statement has been terminated

Apply Primary Key Constraint To The Existing Table

To apply primary key constraint to the existing table, write the following command: -

ALTER TABLE Test Add Primary key (PK_ID)

There can be only one primary key constraint per table. If constraint is not defined while defining table then it must be kept in mind that the column should be not null.

So, table should be defined as: -

Create table Test (
  PK_ID int not null, 
  sName varchar(100)
)

Foreign Key Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Consider an example in which I am defining 2 tables Person and City.

PRIMARY KEY of City (i.e., CityID) is the FOREIGN KEY of Person table

Let us elaborate foreign key constraint. I am declaring 2 tables Person and City.

-- Person 
Create table Person (
  PersonID int, 
  FullName varchar(100), 
  CityID int, 
  Primary key (PersonID), 
  Foreign key (CityID) References City (CityID)
) 
-- City
Create table City (
  CityID int, 
  City varchar(50), 
  primary key (CityID)
)

Note → Here it should be noted that "City" should be declared first then "Person". Because "CityID" will be used as Primary key in the "Person" table. So, existence of "City" table is necessary.

While declaring the person table we have declared foreign key constraint

Foreign key (CityID) References City (CityID)

Now insert a value in the city table

insert into City values(1,'Gurgaon')

So, table City would be

CityID	City
1	Gurgaon

Now insert a value in the Person table

insert into Person values('karan Gupta',1)

So, table Person would be

PersonID	FullName	CityID
1	Karan Gupta	1

Now insert another value in the Person table

insert into Person values('karan',2)

This will give an error

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Person__CityID__1209AD79".

The statement has been terminated.

The reason for this is CityID =2 do not exist in City table.

Posted By -	Karan Gupta

Posted On -	Monday, March 15, 2010

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**