Sometimes you might want to add more than one column as primary key. For example, if you have three columns named Last Name, First Name and Address and there can be duplicate Last Names or duplicate First Names but can never have duplicates in Last Name, First Name and Address combined together
Adding columns to a primary key constraint using T-SQL script:
USE AdventureWorks2012; GO ALTER TABLE Production.TransactionHistoryArchive ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID,[ProductID]); GO
In order to add columns to already existing primary key, you need to drop the primary key constraint first to add columns later.
Drop primary key script is provided below.
USE AdventureWorks2012; GO ALTER TABLE Production.TransactionHistoryArchive DROP constraint [PK_TransactionHistoryArchive_TransactionID]
Adding columns to a primary key constraint using SSMS GUI:
Right click on the Table Name and click on ‘Design’. Hold Ctrl key and select the column names that you want to add as Primary key. Then click on the ‘Set Primary Key’ as shown below.
The below screen shot shows three columns as primary key.
As a quick fact, the maximum number of columns that you can add to the primary key is limited to 16.
Hareesh G
Jayapal Vajrala
Suresh Raavi
DataGinger.com 