Reputation: 2519
I have a table with 3 columns 'Player1', 'Player2' and 'Winner'. I want the 'Winner' column to reference either the 'Player1' or the 'Player2' column. Nothing else should be inserted than an entry of 'Player1' or 'Player2'. I tried it with
ALTER TABLE GAMES
ADD CONSTRAINT fk_winner FOREIGN KEY (WINNER) REFERENCES GAMES (PLAYER1, PLAYER2);
but i got an error.
Upvotes: 0
Views: 637
Reputation: 3867
The number of referencing and referenced columns do not match. You can of course refer two columns in a table. But refer two columns with two columns.
Upvotes: 0
Reputation:
Why not just make a second table PLAYERS that has all unique players. Then in GAMES you could just index into that table with Player1 and Player2. all 3 columns in winner could then have a foreign key constraint that made sense.
Upvotes: 0
Reputation: 1418
Just add a constraint:
ALTER TABLE GAMES
ADD CHECK (WINNER = PLAYER1 OR WINNER = PLAYER2);
Upvotes: 0
Reputation: 60569
Unfortunately you can't create a foreign key like that. If you wanted to enforce this in the database, you would need to use a trigger.
Upvotes: 2