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 one 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 primary 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 manually.
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..
2,141 total views, 1 views today