SQL Composite Key

A SQL composite key is a key that is comprised of two columns. When defining a primary key, we only specify a single column in most cases.

However, we can create a primary key identified by two columns. This is known as a composite key.

Let us learn how we can create a composite key in SQL.

SQL Composite Key Syntax

The following code shows the syntax for creating a composite key in SQL.

CREATE TABLE TABLE_NAME
(col1 TYPE,
col2 datatype,
…
colN TYPE
PRIMARY KEY(col1, col2);

Composite Key in PostgreSQL

The following example shows how to create a table with a composite key in PostgreSQL.

CREATE TABLE products (
product_id serial,
product_name VARCHAR(50),
product_category VARCHAR(50),
quantity INT,
PRIMARY KEY(product_id, product_name)
);

The query above creates a composite key by passing product_id and product_name columns.

NOTE: Composite key creation is relatively similar across major database engines.

SQL Alter Composite Key

To modify the composite key, we can use the ALTER TABLE query as shown below:

ALTER TABLE TABLE_NAME
ADD CONSTRAINT constraint_name PRIMARY KEY (col1, col2);

The code above will add a new constraint with the specified name.

SQL Remove Composite Key

To remove a composite key, you can use the drop command as shown below:

ALTER TABLE TABLE_NAME
DROP CONSTRAINT constraint_name;

The above syntax removes the constraint with the specified name.

Conclusion

This article discussed how to create a composite key in SQL. A composite key is a primary key that is composed of two or more columns.

Thanks for reading

SQL Composite Key