How to Create a Composite Primary Key in SQL Server?

In this article, We will learn what is a Composite Primary Key and How will create a Composite Primary Key. As We know a Primary key is the Candidate key that is selected to uniquely identify a row in a table. A And Primary Key does not allow NULL value. 

Composite Primary Key

When two or more Columns are together Identify the unique row in a table Known as Composite Primary Key. A composite key is a key that is the combination of more than one attribute or column of a given table. It may be a candidate key or a primary key.

We will implement with the help of an example for better understanding, first of all, we will create a database Name of the Database will Sample. and inside the database, we will Create a Compo. 

Step 1: Creating a Database

For database creation, there is query we will use in SQL Platform. 

Query:

Create database sample

Step 2: Use Database

For using the database we will use another query in SQL Platform like Mysql.

Query:

Use Sample 

Step 3: Table Creation with composite primary

We will use the below query to create a Composite Key.

Query:

CREATE TABLE COMPO
(
EMP_ID INT,
DEPT_ID INT,
EMPNAME VARCHAR(25),
GENDER VARCHAR(6),
SALARY INT -->              
//This statement will create a
//composite Primary Key from
  PRIMARY KEY (EMP_ID,DEPT_ID)
  with the help of Column EMP_ID and DEPT_ID
);

Step 4: After the creation of the table, we can Justify the view and metadata of the table with the help of the below Query. It will return Schema, column, data type, and size and constraints.  

Query: 

EXEC sp_help COMPO;

Output:

Step 5: INSERTION DATA IN TABLE.

We will use the below SQL query to insert data in Created Table.

Query:

INSERT INTO COMPO
VALUES (101,001,'RAHUL','MALE',22000),
(102,002,'RAJ','MALE',25000),
(103,003,'PRIYANKA','FEMALE',25500),
(102,003,'VIJAY','MALE',25000),
(101,004,'SHWETA','FEMALE',22000),
(104,003,'SATYA','MALE',23000),
(105,005,'VIVEK','MALE',28000); 

Step 6: Verifying Inserted data

After Inserting data in the table We can justify or confirm which data we have to insert correctly or not. With the help of the Below Query.  

Query: 

SELECT * FROM  COMPO

Output:

Step 7: As We know Primary Key has a unique value but in the above table, EMP_ID has a Duplicate value. Because it’s alone(EMP_ID) is not a primary Key So that it can contain duplicate values. Similarly, DEPT_ID also has duplicate Value because it is also not a Primary Key. But in the above record, EMP_ID and DEPT_ID are not duplicate. Because it is a Composite Primary key. Here (EMP_ID + DEPT_ID) is uniquely Identifying the row in given above table. 

For Finding Unique Value from COMPO, we will Execute like below.

Query:

SELECT EMPNAME,SALARY FROM COMPO WHERE EMP_ID= 102 AND DEPT_ID =6; 

Output: