CODEWITHSUNDEEP
mysqlsqlreference
hacker1337ninja
hacker1337ninja

Reputation: 81

How to reference a entity twice in the same table but with different variables?

I really hope I have used the right vocabulary, since I usually don't do this in English.

My problem is the following: I want to create a table that references to the same entity in another table twice, but with different variables. In detail, I want to create a table called "matches" that references twice to the entity "name" in a table called "club", since a sports match obviously has two teams participating. This is what I tried to do:

CREATE TABLE aclub (
    clubname varchar(100),
    stadium varchar(100),
    foundingdate integer,
    PRIMARY KEY (clubname, stadium) ) ;

CREATE TABLE amatches (
    matchnr integer PRIMARY KEY,
    place varchar(100) REFERENCES aclub(stadium),
    clubname1 varchar(100) REFERENCES aclub(clubname),
    clubname2 varchar(100) REFERENCES aclub(clubname) ) ;

The error I get is the following:

ERROR: there is no unique constraint matching given keys for referenced table "aclub"

Upvotes: 1

Views: 407

Answers (2)

e4c5
e4c5

Reputation: 53734

CREATE TABLE aclub (
    id INT AUTO_INCREMENT PRIMARY KEY,
    clubname varchar(100),
    stadium varchar(100),
    foundingdate integer,
    UNIQUE (clubname),
    UNIQUE (stadium)
) ;

CREATE TABLE amatches (
    matchnr INTEGER AUTO_INCREMENT PRIMARY KEY,
    club1 INTEGER REFERENCES aclub(id),
    club2 INTEGER REFERENCES aclub(id),
    place INTEGER REFERENCES aclub(id)
) ;

So you are have three references to the aclub table as in your question. But now we are using integers instead of varchar and that reduces redundancy

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Use synthetic primary keys. This really makes it easier to design and use the data model:

CREATE TABLE aclub (
    aclubId int auto_increment PRIMARY KEY,
    clubname varchar(100),
    stadium varchar(100),
    foundingdate integer,
    UNIQUE (clubname, stadium)
) ;

CREATE TABLE amatches (
    matchnr integer auto_increment PRIMARY KEY,
    aclubid integer,
    foreign key (aclubid) references aclub(aclubid)
) ;

Your specific problem is that all the columns in a primary key need to be included in the foreign key definition. But why bother? Just use an auto-incremented id for the key and look up the information you want for the other fields.

Upvotes: 1

Related Questions