Are these equivalent for adding a unique constraint?

April 19, 2022

I wonder if the following queries are equivalent.
I want to add a unique constraint for two of the columns in the movie table

CREATE TABLE Movie (
 title VARCHAR(255),
 year INTEGER,
 …
 producerC# INTEGER,
 UNIQUE (title, year)
);

CREATE TABLE Movie (
 title VARCHAR(255) UNIQUE,
 year INTEGER UNIQUE,
 …
 producerC# INTEGER
);

>Solution :

The first version will enforce that the combination of movie title and movie year will always be unique. So, the following two combinations are permitted:

Invasion of the Body Snatchers, 1956
Invasion of the Body Snatchers, 1978

But note that the same movie can appear more than once, provided that it does not do so with the same year.

The second version requires that each movie title and year separately are unique. This would mean that Body Snatchers can only appear once with one given year.

Based on my sample data, I would guess that you want to go with the first version.