How would you create a reference to a table which has a tuple as it's primary key in SQL?-CodePudding

I have 2 tables, one for film and another for review that are built as such:

create table film 
(
    title varchar(20) not null, 
    year int not null, 
    creator int not null, 

    primary key(title, year, creator) 
)

I'm stuck on how to build the review table which has a film object as it's foreign key as the primary key of the table film is a composite one built of several columns.

I tried to create the review table as such:

create table review 
(
    text varchar(100), 
    title varchar(20) not null references film(title), 
    year int not null references film(year), 
    creator int not null references film(creator) 
)

This doesn't work as neither title nor year nor creator have a unique constraint on it. Looking back at it myself, I can see how this relationship would not work.

Any insight would be appreciated !

CodePudding user response：

The syntax is:

create table review
( 
  text     varchar(100), 
  title    varchar(20)   not null, 
  year     int           not null, 
  creator  int           not null,
  constraint fk_review_film foreign key (title, year, creator) 
                        references film (title, year, creator)
);

CodePudding user response：

Just like with the primary key, you cannot do it within the column definition clause, you have to do it as a separate table constraint clause:

create table review ( 
    text varchar(100), 
    title varchar(20) not null, 
    year int not null, 
    creator int not null,
    constraint FK_review_film FOREIGN KEY (title, year, creator)
        REFERENCES film (title, year, creator)

)