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 a 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 look at the records in table 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 identify each rows uniquely in a 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 table.

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

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

Lets create a primary key on Stud_id and Course_id column and it will be 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 primary key in an existing table using alter table command

ALTER TABLE STUD_COURSE ADD PRIMARY KEY (STUD_ID, COURSE_ID)

Now, 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, 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 you try to insert duplicate records 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, the inserted values for the combination of Stud_ID and Course_ID already exists in the table that’s 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 explicitly.

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

Also Read..

Primary key