Are these equivalent for adding a unique constraint?-CodePudding

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
);

CodePudding user response：

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.

CodePudding user response：

No they are different.

 UNIQUE (title, year)

implies that combination of title and year is unique. So title ="alpha", year="2022" and title ="beta", year="2022" are unique as per this .

title VARCHAR(255) UNIQUE,
 year INTEGER UNIQUE,

implies that individual columns must have unique values. So the example above violates the above constraint as year 2022 is repeated.