SQL Composite Key

Filed Under: SQL

In a real-time situation, there can be scenarios when one column is not good enough to uniquely identify a row. In such scenarios, we use a combination of columns to uniquely identify a row.

SQL Composite Key

SQL composite key is a combination of two or more columns in a table that is used to uniquely identify a row. The combination of the columns guarantees uniqueness.

We will try to understand how to create a composite key in the below-mentioned databases.

  1. MySQL
  2. SQLServer
  3. PostgreSQL

MySQL Composite Key

Syntax: –

CREATE TABLE table_name 
(COL1 datatype, 
COL2 datatype, COLn datatype 
PRIMARY KEY (COL1, COL2));

In the syntax above, we can see that the primary key is a combination of two columns.

Let us create a table with a composite primary key.


CREATE TABLE Employee(
employee_no integer,
employee_name character(50),
employee_city character(35),
employee_phn numeric,PRIMARY KEY (employee_no,employee_phn));

In the table above, we are creating the primary key using two columns – employee_no and employee_phn.

Post-execution of the above query check the table property. The following screen will appear.

MySQL Composite Primary Key

MySQL Composite Primary Key

PostgreSQL Composite Key

Syntax: –

CREATE TABLE table_name 
(COL1 datatype, 
COL2 datatype, COLn datatype 
PRIMARY KEY (COL1, COL2));

In the syntax above, we can see that the primary key is a combination of two columns.

Let us create a table with a composite primary key.


CREATE TABLE Employee(
employee_no integer,
employee_name character(50),
employee_city character(35),
employee_phn numeric,PRIMARY KEY (employee_no,employee_phn));

In the table above, we are creating a primary key using two columns – employee_no and employee_phn.

Post-execution of the above query at PGAdmin, the following screen will appear.

PostgreSQL Composite Primary Key

PostgreSQL Composite Primary Key

SQL Server Composite Key

Syntax: –

CREATE TABLE table_name 
(COL1 datatype, 
COL2 datatype, COLn datatype 
PRIMARY KEY (COL1, COL2));

In the syntax above, we can see that the primary key is a combination of two columns.

Let us create a table with a composite primary key.

CREATE TABLE Employee(
employee_no integer,
employee_name character(50),
employee_city character(35),
employee_phn numeric,PRIMARY KEY (employee_no,employee_phn));

Post-execution of the above query at SQL server. The following screen will appear.

SQLServer Composite Primary Key

SQLServer Composite Primary Key

Summary

SQL composite key is used only when a column is not suitable for uniquely identifying a row.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages