CODEWITHSUNDEEP
mysqlinnodb
lazydaemon
lazydaemon

Reputation: 2519

MySQL: How to reference on two columns on the same table?

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

Answers (4)

Ashwin A
Ashwin A

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

user645280
user645280

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

Chris Carew
Chris Carew

Reputation: 1418

Just add a constraint:

ALTER TABLE GAMES
ADD CHECK (WINNER = PLAYER1 OR WINNER = PLAYER2);

Upvotes: 0

Eric Petroelje
Eric Petroelje

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

Related Questions