CODEWITHSUNDEEP
sql
Henk Disser
Henk Disser

Reputation: 1

SQL relation 1 key not 2 keys between tables

I want to define a foreign key constraint between the table Speler and the table Wedstrijd. I want the key on the Wedstrijd table, but when I use this code in my SQL console:

ALTER TABLE Speler
ADD FOREIGN KEY (idSpeler) REFERENCES Wedstrijd(idWedstrijd);

It puts a key on the table Speler and on the table Wedstrijd

Thanks for your time!

Upvotes: 0

Views: 50

Answers (2)

Peter B
Peter B

Reputation: 24147

Instead of "solving" what you perceive as your problem, I think you have a different problem:

By mapping idSpeler to idWedstrijd, you are basically saying that a Speler (Player) is equal to a Wedstrijd (Match). That becomes a 1:1 relation which is then shown as a line with two yellow 'key'-endings (assuming you are using SQL Server).

It is very likely to me that instead you need to create a linking table WedstrijdSpeler that sits between the other two tables.

Then the new table WedstrijdSpeler needs to be given 2 Foreign Keys:

  • WedstrijdSpeler.idWedstrijd -> Wedstrijd.idWedstrijd
  • WedstrijdSpeler.idSpeler -> Speler.idSpeler.

Then you can give WedstrijdSpeler either a combined Primary Key (containing both fields idWedstrijd and idSpeler), or you can add a third field idWedstrijdSpeler and make that the Primary Key. Either approach will do, it is up to you.

Upvotes: 1

Ildelian
Ildelian

Reputation: 1318

ALTER TABLE Wedstrijd
ADD FOREIGN KEY (idWedstrijd) REFERENCES Speler(idSpeler);

Upvotes: 1

Related Questions