Create table with two references in a foreign key

Question

CREATE TABLE Album (
    name          VARCHAR(50),
    lenght    FLOAT,
    genre VARCHAR(30),
    nrSongs INT,

    PRIMARY KEY (name, writer),
    FOREIGN KEY (writer) REFERENCES Musician(name) OR Band(name),
    FOREIGN KEY (Studio) REFERENCES Studio(name)

);

Ok this is what I'm trying to do, I have two tables called Musician and Band, and in the foreign key called writer I want to use the name of a Musician or a Band, but this gives me an error in the OR statement, do you know the correct way to implement this? I can't find it. Thanks :)

Have a look here: http://stackoverflow.com/questions/7844460/foreign-key-to-multiple-tables — deterministicFail, Dec 03 '14 at 16:57

score 1 · Answer 1 · answered Dec 03 '14 at 16:57

Not possible. A foreign key is a direct link between one field in one table, and another field in another table. It is 1:1. You cannot have n:1, 1:n, or n:n mappings in a foreign key definition.

Plus, your overall table definition is invalid. You have no writer field, so your primary key and the writer FK will fail anyways.

For this particular purpose, why not just making everyone a "band". A solo artist is simply a band that happens to have one member.

Thanks, that's a good solution, but I need the two tables, is there no other option? — Pedro, Dec 03 '14 at 17:25

Create table with two references in a foreign key

1 Answers1