Primary keys are a crucial part of relational database management systems (RDBMSs). Most of the time we create a primary key constraint against a single column that is the unique identifier for the table.

But we can also create primary key constraints against more than one column. When we do this, we call it a composite primary key.

Composite keys can be handy when we don’t have a single column that contains unique values, but multiple columns can be combined to create a unique value.

Example of a Composite Primary Key

The following SQL statement creates a table with a composite primary key:

CREATE TABLE Accounts (
   AccountNumber INT NOT NULL,
   AccountType INT NOT NULL,
   AccountDescription VARCHAR(20),
   PRIMARY KEY ( AccountNumber, AccountType )
   );

This example creates a composite primary key against two columns; the AccountNumber and AccountType columns. We did that by using the PRIMARY KEY clause at the end of the CREATE TABLE statement.

In this scenario, the AccountNumber column cannot be guaranteed to be unique – there’s a possibility that more than one row could contain the same value in that column.

Likewise, the AccountType column cannot be guaranteed to be unique either.

But what can be guaranteed, is that the combination of the two columns will be unique.

Example of Usage

Let’s insert some data into our table:

INSERT INTO Accounts( AccountNumber, AccountType, AccountDescription ) 
VALUES( 1, 1, 'High flyer' );
INSERT INTO Accounts( AccountNumber, AccountType, AccountDescription ) 
VALUES( 1, 2, 'Low flyer' );

Result:

mysql> INSERT INTO Accounts( AccountNumber, AccountType, AccountDescription ) 
    -> VALUES( 1, 1, 'High flyer' );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Accounts( AccountNumber, AccountType, AccountDescription ) 
    -> VALUES( 1, 2, 'Low flyer' );
Query OK, 1 row affected (0.00 sec)

Both rows were inserted without any problems, despite the same value being inserted into the AccountNumber column. That’s because a different value was inserted into the AccountDescription column, which resulted in a unique combination.

We could have made a composite key across more than two columns – it’s not limited to just two columns.

Now let’s try to insert data that violates our composite primary key:

INSERT INTO Accounts( AccountNumber, AccountType, AccountDescription ) 
VALUES( 1, 1, 'High five!' );

Result:

ERROR 1062 (23000): Duplicate entry '1-1' for key 'accounts.PRIMARY'

We get an error because that combination of values is already in the composite primary key column.

We can fix the above error by changing the value in one of the columns so that the combination of the two columns is unique.

Example:

INSERT INTO Accounts( AccountNumber, AccountType, AccountDescription ) 
VALUES( 2, 1, 'High five!' );

Result:

Query OK, 1 row affected (0.00 sec)

Success!

It was successful because the combination of 2 and 1 was not currently in the table.

Checking for a Composite Key

Different RDBMSs have different options for examining table structure. The SQL standard defines the information schema as a method for obtaining metadata about the objects on our server. We can use the information_schema.key_column_usage on RDBMSs that support it to return information about the primary keys in our database.

Here’s an example of using the information_schema.key_column_usage table in MySQL to check our table for a composite primary key:

SELECT 
    column_name,
    constraint_name,
    ordinal_position
FROM information_schema.key_column_usage
WHERE table_schema = 'test'
AND table_name = 'Accounts';

Result:

+---------------+-----------------+------------------+
| COLUMN_NAME   | CONSTRAINT_NAME | ORDINAL_POSITION |
+---------------+-----------------+------------------+
| AccountNumber | PRIMARY         |                1 |
| AccountType   | PRIMARY         |                2 |
+---------------+-----------------+------------------+

We can see that our primary key columns are returned, along with PRIMARY in the CONSTRAINT_NAME column. That means they’re the primary key for the specified table. The ORDINAL_POSITION column specifies the column’s position within the constraint (not the column’s position within the table).