CODEWITHSUNDEEP
sqldatabasepostgresql
Pedro
Pedro

Reputation: 43

Create table with two references in a foreign key

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

Upvotes: 0

Views: 77

Answers (1)

Marc B
Marc B

Reputation: 360662

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.

Upvotes: 1

Related Questions