Home » SQL Server Composite Key

SQL Server Composite Key

SQL Server Primary key is used to uniquely identify each rows in the table but sometimes more than one column is used to uniquely identify each rows in the table.




In such case when more than one columns are used to create a primary key is called as composite key.

SYNTAX

CREATE TABLE TABLE_NAME ( 

COLUMN_1 DATA_TYPE_1,

COLUMN_2 DATA_TYPE_2,

COLUMN_2 DATA_TYPE_3,
…
PRIMARY KEY (COLUMN_1, COLUMN_2, …));

 

To demonstrate this , Lets create a sample table named as STUD_COURSE and insert some records into it.

CREATE TABLE STUD_COURSE(
STUD_ID INT NOT NULL,
COURSE_ID INT NOT NULL,
BATCH_NO VARCHAR(10) NOT NULL,
REG_DATE DATETIME NOT NULL
)

INSERT INTO STUD_COURSE
( STUD_ID, COURSE_ID , BATCH_NO , REG_DATE)
VALUES
(101, 51, '101-X', '2019-01-01'),
(101, 52, '102-XI', '2019-06-05'),
(102, 52, '102-XI', '2019-06-05'),
(102, 51, '102-X', '2019-01-09'),
(101, 53, '103-XII', '2019-12-05'),
(103, 52, '102-XX', '2019-06-05'),
(104, 53, '103-XII', '2019-12-05'),
(105, 55, '106-X', '2019-05-01'),
(106, 55, '106-IX', '2019-05-01'),
(106, 51, '106-X', '2019-01-01')

 

Lets observe the table records in order to find a qualify primary key column.

SELECT * FROM STUD_COURSE

As you can see, in this table you do not have an individual column that can be used to uniquely identify each rows uniquely in the table, as each column are having some duplicate records.

So, in order to make each rows unique, you can create a primary key by including more than one column to identify each rows uniquely in the table.

Lets observe the table records to identify more than columns those can be used to uniquely identify the table records.

You can see, using the combination of Stud_Id and Course_Id coulmns , can be identified each rows uniquely in the table.

A student can not take same course multiple time, so for the combination of Stud_ID and Course_Id it will be easy to identify each rows uniquely in the table.

Lets create a primary key on stud_id and course_id column that is considered as composite key.




Create a primary key on multiple columns while creating a new table

 
CREATE TABLE STUD_COURSE(

STUD_ID INT NOT NULL,

COURSE_ID INT NOT NULL,

BATCH_NO VARCHAR(10) NOT NULL,

REG_DATE DATETIME NOT NULL,

PRIMARY KEY (STUD_ID, COURSE_ID)

)

Adding a primary key on an existing table 

You can add primay key in an existing table using alter table command

 

ALTER TABLE STUD_COURSE ADD PRIMARY KEY (STUD_ID, COURSE_ID)

You can check , to confirm whether a primary key is created on the combination of both column or not .

Following SP_HELP command is used to get the table schema in terms of table structure, columns details, indexes , constraints and so on..

 

SP_HELP STUD_COURSE

As you can see, a primary key is created on combined stud_Id and Colurse_ID.




Error – Violation of primary key

It gives an error when duplicate records are tried to insert for the combination of stud_id and course_Id as shown below.

INSERT INTO STUD_COURSE
( STUD_ID, COURSE_ID , BATCH_NO , REG_DATE)
VALUES
(101, 51, '101-X', '2019-01-01')

As you can see, rthe values for the stud_id and Course_ID as combination is already exists in the table thats why it gives an error.

 

DROP PRIAMRY KEY CONSTRAINT

You can drop primary key constraint using  ALTER TABLE DROP COMMAND

To drop a primary key constraint , you need a constraint name first.

Whenever a constraint is created , SQL Server gives a default name to this until you specify a name manullay.

Here you can see, the below result of sp_help command wherein a primary key constraint name is PK__STUD_COU__41F2D3D8B810542A

Which you can get easily using sp_help command.

Once you run sp_help command, you can see it gives you all the details of table , and there is a primary key constraint name.

 

SP_HELP STUD_COURSE

 

 

Now you can use following command to drop a primary key constraint from table.

ALTER TABLE STUD_COURSE DROP CONSTRAINT PK__STUD_COU__41F2D3D8B810542A

 

Recommended for you

Primary key

 




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.