Composite key in SQL Server

Question

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........

Senthilkumar · 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 geetha · Answer

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 Saini · Answer

Thanks Senthil....

Vulpes · Answer

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

Composite key in SQL Server

Answers (4)