Articles → SQL SERVER → Constraints In SQL Server

Constraints In SQL Server

In this article, we will discuss constraints in the SQL server.

What Is A Constraint?

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

Primary Key Constraint

A primary key column: -

Contains unique value
It should not be null

When we apply the primary key constraint to any column, both the rules mentioned above are 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 applying the 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 the 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 the constraint is not defined while defining the table then it must be kept in mind that the column should be not null.

So, the 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. The

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

Let us elaborate on foreign key constraints. 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 the Primary key in the "Person" table. So, the existence of a "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

Insert a value in the Person table.

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

So, a 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	**