Mysql select composite primary key

What Is a Composite Key in SQL? 

A composite key in SQL can be defined as a combination of multiple columns, and these columns are used to identify all the rows that are involved uniquely. Even though a single column can’t identify any row uniquely, a combination of over one column can uniquely identify any record. In other words, the combination key can also be described as a primary key that is being created by using multiple columns. However, the data types of different columns could differ from each other. You can also combine all the foreign keys to create a composite key in SQL.

Syntax to declare a composite key in SQL:

------ Syntax to create a composite key ------

------ by combining 3 columns: COL1, COL2, COL3 -----

CONSTRAINT COMPOSITE_KEY_NAME PRIMARY KEY (COL1, COL2, COL3)

Description:

• COMPOSITE_KEY_NAME: This is the name of the new composite key created by combining two or more columns. 

Note that the data type of all the columns in a composite key can be different.

• Columns: The latest version of SQL supports the combining of more than 16 columns. The data type of the columns combined to make a composite key can be different for all the columns. 

When Does the Composite Key Come to the Picture? 

You already saw that the composite keys are used to identify all the rows that are involved uniquely. Composite keys in SQL prove to be useful in those cases where you have a requirement of keys that can uniquely identify records for better search purposes, but you do not possess any single unique column. In such cases, you must combine multiple columns to create a unique key.

Now, understand this concept with an example for better understanding. Suppose you are handling the data of the employees of a company and you want to search for an employee named Rahul with his name in your database. But in such cases of searching by name, there is a high possibility that more than one employee shares the same name. This happened in this case, too. You ran into multiple employees that share the name, Rahul. Now to overcome this issue, since you already know that the employee numbers are always unique, you can optimize your search by considering the name column along with the column of employee number as a single column. This scenario is one of the use cases of Composite keys. 

Syntax to create a composite key for a table in SQL:

---- Syntax to create a composite key for a table

---- by combining some columns

Create table table_name (

COL1 data_type_1 NOT NULL,

COL2 data_type_2 NOT NULL,

COL3 data_type_3,

COL4 data_type_4,

COLN data_type_n, 

----- Declare the composite key -----

---- here COL1, COL3, and COL4 are ----

---- forming up the composite key ----

CONSTRAINT COMP_NAME PRIMARY KEY (COL1, COL3, COL4)     

);

Consider an example in which you will understand the concept of composite keys in SQL using a STUDENT table.

Table STUDENT:

In the above table, you can notice that there is no single column that has unique values. But by combining the columns: S_Name and Parent_Contact_No, you can achieve the task of getting a unique column.

SQL Queries to create the above table and declare a composite key using the columns of that table.

 ---- create a table named STUDENT ----

Create table STUDENT 

(

    ---- declaring columns with different data types ----

S_Name varchar(30),  

S_Class integer,  

Parent_Contact_No integer,

S_Age integer,  

---- declare the composite key ----

---- columns: S_Name and Parent_Contact_No ----

---- are combined to create the composite key ----

---- both columns have different data types ----

CONSTRAINT My_Composite_Key PRIMARY KEY (S_Name, Parent_Contact_No)

);

---- add values to the table ----

Insert into STUDENT values('Mehul', 6, 8700867330, 11);

Insert into STUDENT values('Rashi', 5, 8700867330, 10);

Insert into STUDENT values('Mehul', 6, 9990155289, 11);

Insert into STUDENT values('Vansh', 7, 9354226009, 12);

Insert into STUDENT values('Ishita', 9, 8681012213, 14);

 ---- select all values of the table ----

---- to display the content ----

Select * from STUDENT

How to Use the Composite Key in SQL?

The composite key differs from a primary key and it is important to know how the two vary, both in syntax and usage. One can say that combining a set of multiple columns in a table, results in a primary key, as it will have a unique value in every row. 

• Primary Key Declaration

The primary key is a constraint that recognizes all of the columns in a row as unique. For a constraint to be recognized as a primary key, it must contain unique values throughout the row and none of the values must be NULL. In a table, there can only be one primary key. A primary key can have one or as many columns as possible.

Syntax to create a primary key in SQL:

• At the Column Level:

---- create a table ----

Create table Table_Name 

(

    COL1 data_type1 NOT NULL,

    COL2 data_type2 NOT NULL,

    COL3 data_type3 

   ---- declare the primary key ----

   ---- after declaring all columns ----

    PRIMARY KEY (COL1)

);

• At the Row-Level:

---- create a table ---- 

Create table Table_Name 

(

   ---- declare the primary key ----

   ---- while declaring the columns ----

    COL1 data_type1 PRIMARY KEY,

    COL2 data_type2 NOT NULL,

    COL3 data_type3,

);

The following examples illustrate two methods to create a primary key in SQL.

Method 1:

After the creation of all the columns in the table.

---- create a table named STUDENT ---- 

Create table STUDENT 

(

    ---- declaring columns with different data types ----

S_ID integer NOT NULL,

S_Name varchar(30) NOT NULL,    

S_Class integer,  

Parent_Contact_No integer,

S_Age integer,  

---- declare the primary key ----

PRIMARY KEY (S_ID)                 

);

Method 2:

While creating a column in the table.

---- create a table named STUDENT ----

Create table STUDENT

(

---- declare the primary key -----

------ while creating the column ----

S_ID integer PRIMARY KEY,   

S_Name varchar(30),  

S_Class integer NOT NULL,  

Parent_Contact_No integer,

S_Age integer,

);

• Composite Key Declaration

When over one column or field in a table are combined to achieve the task of uniquely identifying row values, then that composite key can be either a primary or a candidate key of that table.

Syntax to create a composite key in SQL:

---- create a table ----

Create table table_name (

COL1 data_type_1 NOT NULL,

COL2 data_type_2 NOT NULL,

COL3 data_type_3,

COLN data_type_n,

 ----- Declare the composite key -----

CONSTRAINT COMP_NAME PRIMARY KEY (COL1, COL3, COL4)     

);

Examples of Composite Keys in SQL.

Let us understand composite keys with the help of the following examples:

Example 1: Consider Tables Buyer and Item:

Table BUYER:

Here, you can see that the column B_ID identifies all table records uniquely, so it is the primary key of the table BUYER. And this column will act as the foreign key of another table ITEM.

SQL queries for the table BUYER.

Create table BUYER

(

    ---- declaring columns of the table ---- 

B_ID int NOT NULL,

B_Name varchar (30),

B_Contact_No int,

B_Address varchar (50),

---- declare the primary key ----

PRIMARY KEY (B_ID)   

);

---- add values to the table ----

Insert into BUYER values(101, 'Mohan', 8700867330, 'Delhi');

Insert into BUYER values(102, 'Pankaj', 8681012213, 'Noida');

Insert into BUYER values(103, 'Diya', 8090100224, 'Saket');

Insert into BUYER values(104, 'Rohit', 8090100224, 'Gurugram');

Insert into BUYER values(105, 'Gaurav', 9990155289, 'Noida'); 

---- select all values of the table ----

---- to display the content ----

Select * from BUYER

Table ITEM:

B_ID, Order_No, I_ID are combined to form the composite key.

In the table ITEM, the column B_ID acts as a foreign key, and there is no column that can act as the primary key. This means that there is no field that can be used to identify all the table records uniquely. So to make a primary key, you can create a composite key by combining the columns: B_ID, Order_No, and I_ID. Together combined, these 3 columns can become a primary key for the table ITEM.3

SQL queries for the table ITEM.

---- create a table named ITEM ----

Create table ITEM

(

    ---- declaring columns of the table ----

B_ID int NOT NULL,

Order_No int,

I_ID int(10),

I_Name varchar (20),

I_Quantity int,

---- declare the composite key ---- 

CONSTRAINT MyCompositeKey PRIMARY KEY (B_ID, Order_No, I_ID)

);

---- add values to the table ----

Insert into ITEM values(101, 1, 201, 'Laptop', 1);

Insert into ITEM values(102, 2, 205, 'HeadPhones', 2);

Insert into ITEM values(103, 3, 202, 'Mobile', 2);

Insert into ITEM values(102, 2, 202, 'Keyboard', 1);

Insert into ITEM values(101, 1, 204, 'Speaker', 4);

---- select all values of the table ----

---- to display the content ----

Select * from ITEM

Example 2: Consider the Table Student:

Table STUDENT:

S_Name, S_Class, Parent_Contact_No are combined to form the composite key.

The table depicted above represents the records of the students with their fee payment status. Here, also you can notice that no individual column can be used to identify all values of the table as unique. However, you can combine the first three columns: S_Name, S_Class, and Parent_Contact_No to create a composite key.

SQL queries for the table ITEM.

---- create a table named STUDENT ----

Create table STUDENT 

(

    ---- declaring columns with different data types ----

S_Name varchar (30),  

S_Class integer,  

Parent_Contact_No integer,

Payment_Status varchar (4),  

---- declare the composite key ----

---- columns: S_Name, S_Class, and Parent_Contact_No ----

---- are combined to create the composite key ----

---- all three columns have different data types ----

CONSTRAINT My_Composite_Key PRIMARY KEY (S_Name, S_Class, Parent_Contact_No)

);

---- add values to the table ----

Insert into STUDENT values('Mehul', 6, 8700867330, 'Yes');

Insert into STUDENT values('Rashi', 5, 8700867330, 'Yes');

Insert into STUDENT values('Mehul', 6, 9990155289, 'No');

Insert into STUDENT values('Vansh', 7, 9354226009, 'Yes');

Insert into STUDENT values('Ishita', 9, 8681012213, 'No');

---- select all values of the table ----

---- to display the content ----

Select * from STUDENT

How to Alter and Drop Composite Keys?

Like any other column in a table, composite keys can also be operated with the alter commands. You can easily perform add or drop operations in a table by using the alter command with the composite key in SQL.  To add additional columns in the set of columns forming the composite key, you can use the alter-add command. And to delete a column from the set of columns combined together to form the composite key, you can use the alter-drop command.

• ALTER-ADD command: To add additional columns to the existing set of columns forming the composite key.

Syntax 

---- syntax to add columns to an ----

---- existing composite key ----

ALTER table table_name

ADD CONSTRAINT Constraint_name PRIMARY KEY (COL1, COL2, COLN);

---- this will add COL1, COL2, and COLN ----

---- to the composite key of the table ----

Example

---- syntax to add columns to an ----

---- existing composite key ----

---- of the table STUDENT ----

ALTER table STUDENT

ADD CONSTRAINT MyCompKey_Scores PRIMARY KEY ( Scores, Percentage );

---- this will add columns named ----

---- Scores and Percentage ----

---- to the composite key of the table ----

• ALTER-DROP command: To delete a column from the existing set of columns forming the composite key.

Syntax 

---- syntax to delete a column from an ----

---- existing composite key ----

ALTER table table_name

DROP CONSTRAINT Constraint_Name;

---- this will delete the constraint ----

---- from the composite key of the table ----

Example

---- syntax to delete a column from an ----

---- existing composite key ----

---- of the table STUDENT ----

ALTER table STUDENT

DROP CONSTRAINT MyCompKey_Scores;

---- this will delete the constraint ----

---- from the composite key of the table ----

How to Create Composite Keys in Mysql, SQL Server, and Postgresql? 

MySQL

Syntax

---- Syntax to create a composite key for a table

---- by combining some columns

Create table table_name (

COL1 data_type_1,

COL2 data_type_2,

COL3 data_type_3,

COLN data_type_n,

----- Declare the composite key -----

---- here COL1 and COL3 are ----

---- forming up the composite key ----

PRIMARY KEY (COL1, COL)     

);

Example

Create table STUDENT 

(

    ---- declaring columns with different data types ----

S_ID integer,  

S_Class integer,  

S_Age integer,

S_Phone integer,  

---- declare the primary key ----

PRIMARY KEY (S_ID, S_Phone)                 

);

SQL Server

Syntax

---- Syntax to create a composite key for a table

---- by combining some columns

Create table table_name (

COL1 data_type_1,

COL2 data_type_2,

COL3 data_type_3,

COLN data_type_n,

----- Declare the composite key -----

---- here COL1 and COL3 are ----

---- forming up the composite key ----

PRIMARY KEY (COL1, COL)     

);

Example

Create table STUDENT 

(

    ---- declaring columns with different data types ----

S_ID integer,  

S_Class integer,  

S_Age integer,

S_Phone integer,  

---- declare the primary key ----

PRIMARY KEY (S_ID, S_Phone)                 

);

Postgresql

Syntax

---- Syntax to create a composite key for a table

---- by combining some columns

Create table table_name (

COL1 data_type_1,

COL2 data_type_2,

COL3 data_type_3,

COLN data_type_n,

----- Declare the composite key -----

---- here COL1 and COL3 are ----

---- forming up the composite key ----

PRIMARY KEY (COL1, COL)     

);

Example

Create table STUDENT 

(

    ---- declaring columns with different data types ----

S_ID integer,  

S_Class integer,  

S_Age integer,

S_Phone integer,  

---- declare the primary key ----

PRIMARY KEY (S_ID, S_Phone)                 

);

Become job-ready with a globally recognized Business Analyst Certification Course. Sign-up today and enrich your career.

Wrapping Up!

In this article, you have learned about the composite key in SQL. This article looked into the basics of composite keys in SQL and how to use them. You dived deep into the primary key and composite keys, their usage, and examples. It also discussed examples of how to create composite keys in MySQL, SQL Server, and Postgresql. 

One of the best ways to learn the concepts of SQL in a detailed and hands-on manner is to take up the SQL Certification Training Course provided by Simplilearn. With the help of this training course, you will be able to master SQL and improve your career aspects. This course covers basics to advanced topics such as Database, relationships, query tools, SQL commands, aggregate functions, transaction controls, views, procedures, etc. Mastering these topics will open doors to lucrative careers such as a Business Analyst, Data Analyst, etc. 

If you have any questions for us, leave them in the comments section of this article. Our experts will get back to you on the same, ASAP!

Happy Learning!