Introduction
UNIQUE
9. Multiple UNIQUE constraints
NOT NULL
CHECK
DEFAULT
Summary

Instruction

Good. As you can see, UNIQUE on multiple columns works exactly the way PRIMARY KEY does.

There is, however, one major difference between the two. We can only have one primary key in a given table, but we can have as many UNIQUE constraints as we want. Take a look at the example:

CREATE TABLE board_game (
    id IDENTITY UNIQUE,
    name varchar(32),
    genre varchar(32),
    studio varchar(32),
    min_players int,
    min_age int,
    price decimal(5,2),
    UNIQUE (name, genre),
    UNIQUE (name, studio)
);

We now have one row with the UNIQUE constraint and two separate UNIQUE constraints, each containing two columns.

Is there a difference between one UNIQUE constraint with multiple columns and separate UNIQUE constraints for each column? There is. In the former case, all the columns must form a unique group of values. In the latter, each value in each column must be unique independently.

Exercise

We modified our table so that there are now two UNIQUE constraints. Try to test the table as previously: add rows with the same and different values and see what is allowed.

Console

Code editor

Result

TableConsole