CODEWITHSUNDEEP
sqlsql-server
KaiZ
KaiZ

Reputation: 391

How do I make a foreign primary key that references to another primary key that is made of multiple foreign keys?

I've currently got something like this on one table:

create table NetWorth (
    id int,
    id_movie int foreign key references Movie(id),
    primary key (id, id_movie)
    )

And want to make a reference to its primary key, which is made of up 2 attributes, its own ID and the ID of the Movie table. Currently, I was thinking something like this:

create table Merchandising (
    id_networth int foreign key references NetWorth(id) primary key,
    value float
    )

But obviously it's wrong, because it's missing the second key reference, and I don't know how to implement it in the second table. So could you guys help me out?

Upvotes: 0

Views: 11470

Answers (2)

sstan
sstan

Reputation: 36473

As you mention in the comments, NetWorth.Id is unique, and so is probably the only column you should define as the primary key, not id, id_movie.

Once you make that change, you will no longer have an issue creating your foreign key reference from the Merchandising table.

EDIT:

Your table creation statements could look like this (though I agree with marc_s that you should explicitly name the constraints):

create table NetWorth (
  id int primary key,
  id_movie int foreign key references Movie(id)
)

create table Merchandising (
  id_networth int foreign key references NetWorth(id) primary key,
  value float
)

Upvotes: 0

marc_s
marc_s

Reputation: 754268

If the primary key you want to reference is made up from multiple columns, all foreign keys referencing it must also have and use all those columns. You can't just reference half a primary key - it's all or nothing.

Since the FK references two columns in your case, you cannot apply the FOREIGN KEY constraint syntax to a single column - use this instead:

create table Merchandising 
(
    id_networth int 
       constraint PK_Merchandising primary key,
    id_movie int,
    value float,

    constraint FK_Merchandising_Networth
       foreign key(id, id_movie) references NetWorth(id, id_movie) 
)

And I would also recommend to always specify an explicit name for your constraints - both your primary key constraints, as well as your foreign key constraints (and also all others you might have).

Upvotes: 3

Related Questions