Composite primary key in SQL Server

Primary Key:

A column which is used to identify the records uniquely is referred to as Primary Key.

1. Primary key won't allow Null values.
2. If a Primary key is created, It will automatically create the clustered index on the table. In turn, the data will be sorted based on this column.
3. Only one Primary key can be created for a table.

Composite Primary Key:

1. Multiple columns can participate in the Primary Key which is referred to as composite primary key.

Am creating the table with Composite Primary key on the column ID and Name

CREATE TABLE VENKAT_SAMPLE_TABLE ( ID INT, NAME VARCHAR(100),BIRTHDATE DATETIME
,PRIMARY KEY(ID,NAME))

To get the information about your Primary key on the columns.You can use the below system view.

SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE


In one of the popular forum, I have seen a query. How to achieve it your designer window. Right click on the table and you will get the columns. Press control or Shift key and select the columns -> Right click and you can set the primary key.

Cheers,
Venkatesan Prabu .J