Composite key in SQL Server

Vineet Kumar Saini

14y7.8k

Hi...

I know the concept of primary key and foreign key but I want to know the concept of composite key ? Please explain anyone with an example ?

Thanks........

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Senthilkumar14y ago
Accepted answer
Hi Vineet,

The key formed by combining two or more columns to enforce uniqueness of the row is called composite key.

For example,

I have created the ProductsMaster table. Lets take an example for "Soap" is a product. There can be more than one soap in the column but it should be different manfacturer. Here we need to make the product name and manfacturer is unique column.

CREATE TABLE ProductMaster
(
ProductID INT IDENTITY(1,1)
,ProductName VARCHAR(100)
,Manfacturer VARCHAR(100)
,CreatedDate DATETIME DEFAULT GETDATE()
PRIMARY KEY(ProductName,Manfacturer)
)

--SELECT * FROM ProductMaster

INSERT INTO ProductMaster(ProductName,Manfacturer)
VALUES('Soap','Hamam')

INSERT INTO ProductMaster(ProductName,Manfacturer)
VALUES('Soap','Hamam') --Error

INSERT INTO ProductMaster(ProductName,Manfacturer)
VALUES('Soap','Lux')

In the above example it can be any number of soap in the product columns but it should have different manfacturers. When we try try to insert the combination of same product name and manfacturer then it will throw the duplicate insert error.

Here two columns enforces the uniqueness of the columns which typically table has two keys. It is called composite key.

If it is useful then mark it as "Accepted Answer".
geetha geetha4y ago
Composite Key:

Composite key is a key which is the combination of more than one field or column of a given table.

Create table Stud_attendance (

STU_NAME VARCHAR(25) NOT NULL,

DATE_VALUE DATE NOT NULL,

CLASS_PERIOD INT NOT NULL,

PRESENCE_OF_STUDENT VARCHAR(15),

CONSTRAINT COMP_K PRIMARY KEY (STU_NAME, DATE_VALUE, CLASS_PERIOD)

);

Here in the above example, we are deriving the attendance of students present in each day. Here the columns “STU NAME”,” DATE”, “CLASS PERIOD” and “PRESENCE OF STUDENT” individually won’t be able to fetch a row as they consist of duplicates.

In the above example, we will consider “STU NAME”, “DATE” and “CLASS PERIOD” combine as a composite key, which will be used to fetch the rows from the table.
Vineet Kumar Saini14y ago
Thanks Senthil....
Vulpes14y ago
A composite key is a key which consists of not just one but a combination of columns which produces a unique result.

There's an example here:

http://sqlzoo.net/howto/source/z.dir/tip241027/sqlserver