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.
- MySQL
- SQLServer
- PostgreSQL
MySQL Composite Key
Syntax: –
|
1 2 3 4 |
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.
|
1 2 3 4 5 6 7 |
CREATE TABLE Employee( employee_no <span class="hljs-built_in">integer</span>, 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
PostgreSQL Composite Key
Syntax: –
|
1 2 3 4 |
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.
|
1 2 3 4 5 6 7 |
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
SQL Server Composite Key
Syntax: –
|
1 2 3 4 |
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.
|
1 2 3 4 5 |
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
Summary
SQL composite key is used only when a column is not suitable for uniquely identifying a row.