Concept Of Composite Primary Key In SQL Server 2008
In this article I tell you how to create a composite primary key in SQL Server.
Introduction
In this article I tell you how to create a composite primary key in SQL Server.
First of all I describe about primary key. A column which is used to identify each row in the table uniquely is referred to as Primary Key( PK ). Primary key won't allow Null values and it enforces the entity integrity of the table. A table can have only one PRIMARY KEY constraint.
Composite Primary Key
If a PRIMARY KEY constraint is defined on set of columns in a table, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique. That combination of column is referred as Composite Primary Key. Follow the steps given below.
Step 1
Consider the following design:
create table CustomerProducts
(
ProductID int not null,
VendorID int not null,
OrderLimit int not null,
Primary key (ProductID, VendorID)
)
Here we have defined a composite (multiple column) primary key as the combination of the ProductID and VendorID columns. This makes sure that the combination of ProductID and VendorID is unique. Now try to insert some values in table.
Records get inserted successfully.
Step 2
Now try to insert the duplicate values for the ProductID and VendorID columns. An error is occurring because combination of ProductID and VendorID must be unique. so record will not inserted.
Step 3
To get the information about your Primary key on the columns. You can use the below system view.
