SQL Standard

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

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list