CODEWITHSUNDEEP
sqloracle-databaseduplicates
SJ19
SJ19

Reputation: 2123

Oracle SQL: Constraint foreign key with multiple values from same column: duplicate column name error

I want to create a battle table referencing to 2 players.

CREATE TABLE Battle
(
    player_1            NUMBER(10),
    player_2            NUMBER(10),
    CONSTRAINT PK_Battle PRIMARY KEY(player_1, player_2),
    CONSTRAINT FK_Battle_Player FOREIGN KEY(player_1, player_2) REFERENCES Player(id, id)
);

But appearantly I can't have duplicate column names, I've tried "Player(id)" as well but it doesn't work either, any idea?

SQL Error: ORA-00957: duplicate column name
00957. 00000 -  "duplicate column name"

Upvotes: 0

Views: 1075

Answers (1)

ruudvan
ruudvan

Reputation: 1371

You would need to create two different foreign keys since you are not referencing the same row in the parent table, but two independent rows.

Something like - 

CREATE TABLE Battle
(
    player_1            NUMBER(10),
    player_2            NUMBER(10),
    CONSTRAINT PK_Battle PRIMARY KEY(player_1, player_2),
    CONSTRAINT FK_Battle_Player1 FOREIGN KEY player_1 REFERENCES Player(id),
    CONSTRAINT FK_Battle_Player2 FOREIGN KEY player_2 REFERENCES Player(id)
);

Upvotes: 2

Related Questions