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(11) ProductName VARCHAR(100) Manfacturer VARCHAR(100) CreatedDate DATETIME DEFAULT GETDATE() PRIMARY KEY(ProductNameManfacturer) ) --SELECT * FROM ProductMaster INSERT INTO ProductMaster(ProductNameManfacturer) VALUES('Soap''Hamam') INSERT INTO ProductMaster(ProductNameManfacturer) VALUES('Soap''Hamam') --Error INSERT INTO ProductMaster(ProductNameManfacturer) 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.

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