Skip to content

Composite Key

As we have learned about the primary keys and foreign keys, we have only now used it in one field. But there is more to learn in this field: keys can also be used in multiple fields at the same time. Composite key is a combination of two or more columns in a table that can be uniquely identify the records in the table.

Getting Started

Let's create a database and use it so we can try out how composite keys work:

MySQL
CREATE DATABASE compositetest;
USE compositetest;

Example 1: 🚘 Cars

In order to create a table that can hold cars and in that table each car could be uniquely identified by it's manufacturer and model, we could do this:

MySQL
CREATE TABLE cars(
    manufacturer VARCHAR(100) NOT NULL,
    model VARCHAR(100) NOT NULL,
    PRIMARY KEY (manufacturer, model)
);

Now, try doing DESCRIBE cars and you should see this:

You can see that the manufacturer and model both form up the primary key for this table.

Let's try adding couple records into the table now:

MySQL
INSERT INTO cars (manufacturer, model) VALUES
    ("Ford", "Mondeo"),
    ("Ford", "Kuga");

And we can see that this worked. Let's try adding the Ford Mondeo again:

MySQL
INSERT INTO cars (manufacturer, model) VALUES
    ("Ford", "Mondeo");

And we'll get an error saying about a duplicate entry. So – we can have multiple cars with the same name and even multiple cars with the same manufacturer, but we cannot have multiple cars with this combination of the same manufacturer and model.

Example 2: Persons

Let's do another example. In this case, we would store information about person addresses. In this example you can see that the unique constraint can be also used as composite key:

MySQL
CREATE TABLE persons(
    email VARCHAR(150) NOT NULL,
    streetAddress VARCHAR(200) NOT NULL,
    zipcode VARCHAR(20) NOT NULL,
    country VARCHAR(70) NOT NULL,
    PRIMARY KEY (email),
    UNIQUE (streetAddress, zipcode, country)
);

And then let's try adding couple users into the table:

MySQL
INSERT INTO persons (email, streetAddress, zipcode, country) VALUES
  ("a@b.com", "street 1", "11111", "Finland"),
  ("a@c.com", "street 1", "11111", "Kenya");

See that now each email address needs to be unique and streetAddress, zipcode and country needs to be compositely unique. So we could even have the same address in some country, but the zipcode is different so this is allowed.

If we again try to add this address again, we get the duplicate entry error:

MySQL
INSERT INTO persons (email, streetAddress, zipcode, country) VALUES
  ("a@d.com", "street 1", "11111", "Finland");

Removing the Database

If you no longer want to play around with this this database, as a last thing, remove the database that you created:

MySQL
DROP DATABASE dbnull;

Note on controversy with composite keys

Composite keys with relational databases, mostly used with primary keys, are a controversial topic. By Googling around should you use composite keys you can find a lot of examples why they should not be used and some claim that you should use them. This is a religion war of it's own kind.

My own opinion is that this is pretty much a design opinion that you can apply yourself. I personally prefer to only use that one ID column in each table that identifies the records. This makes foreign key references easier to create.

If you feel like you want to use composite primary keys in your tables, do it. But then when you start doing it, notice that you should use it always when available. This is for consistency.

Adding the UNIQUE composite keys on the other hand is much better practice, that you should apply always when required.

Conclusion

  • Composite keys can be used to chain multiple columns together to be unique.

Learn more