CODEWITHSUNDEEP
sqldatabaseoracle-database
Saad
Saad

Reputation: 195

Database SQL Error: ORA-02270: no matching unique or primary key for this column-list

I am creating a simple database and I keep encountering this error, ORA-02270: no matching unique or primary key for this column-list. I have been trying to solve this for more than a day and I don't understand what I'm doing wrong. I have looked at other posts regarding this error but don't understand what I'm doing wrong here.

The tables that create without any issue are the TEAMS, PLAYERS, and GAMES tables. When I try to create the TEAM_GAME_STATS or the PLAYER_GAME_STATS tables, I get this error. I noticed that if I remove the foreign key constraints from both of these tables, then there is no issue creating them; but I'm thinking that might cause issues with the data? I would appreciate if someone could look over my code and inform me of what I might be doing wrong or point me in the right direction. I have simplified the tables to only the primary/foreign keys, so there isn't an excessive amount of code.

CREATE TABLE teams (
  team    VARCHAR2(50),
  CONSTRAINT teams_pk PRIMARY KEY (team)
);

CREATE TABLE players (
  player_id          NUMBER,
  team               VARCHAR2(50),
  CONSTRAINT players_pk PRIMARY KEY (player_id),
  CONSTRAINT players_fk_team FOREIGN KEY (team)
    REFERENCES teams (team)
);

CREATE TABLE games (
  game_number   NUMBER,
  player_id     NUMBER,
  team          VARCHAR2(50),
  CONSTRAINT games_pk PRIMARY KEY (game_number, player_id, team),
  CONSTRAINT games_fk_player_id FOREIGN KEY (player_id)
    REFERENCES players (player_id),
  CONSTRAINT games_fk_team FOREIGN KEY (team)
    REFERENCES teams (team)
);

CREATE TABLE team_game_stats (
  game_number               NUMBER,
  team                      VARCHAR2(50),
  CONSTRAINT team_game_stats_pk PRIMARY KEY (game_number, team),
  CONSTRAINT team_game_stats_fk_game_number FOREIGN KEY (game_number)
    REFERENCES games (game_number),
  CONSTRAINT team_game_stats_fk_team FOREIGN KEY (team)
    REFERENCES games (team),
);

CREATE TABLE player_game_stats (
  game_number               NUMBER,
  player_id                 NUMBER,
  CONSTRAINT player_game_stats_pk PRIMARY KEY (game_number, player_id),
  CONSTRAINT player_game_stats_fk_game_number FOREIGN KEY (game_number)
    REFERENCES games (game_number),
  CONSTRAINT player_game_stats_fk_player_id FOREIGN KEY (player_id)
    REFERENCES games (player_id),
);

CREATE INDEX players_team_idx
  ON players (team);

CREATE INDEX games_player_id_idx
  ON games (player_id);
CREATE INDEX games_team_idx
  ON games (team);

CREATE INDEX team_game_stats_game_number_idx
  ON team_game_stats (game_number);
CREATE INDEX team_game_stats_team_idx
  ON team_game_stats (team);

CREATE INDEX player_game_stats_game_number_idx
  ON player_game_stats (game_number);
CREATE INDEX player_game_stats_player_id_idx
  ON player_game_stats (player_id);

Upvotes: 0

Views: 77

Answers (1)

Peter M.
Peter M.

Reputation: 713

A foreign key is a reference to another table's primary key.

Oracle SQL: Receiving 'no matching unique or primary key' error and don't know why

Change

CONSTRAINT games_pk PRIMARY KEY (game_number, player_id, team)

to

CONSTRAINT games_pk PRIMARY KEY (game_number, team)

Also change

CONSTRAINT team_game_stats_fk_game_number FOREIGN KEY (game_number)
REFERENCES games (game_number),
CONSTRAINT team_game_stats_fk_team FOREIGN KEY (team)
REFERENCES games (team),

to be

CONSTRAINT team_game_stats_game_fk FOREIGN KEY (game_number, team)
REFERENCES games (game_number, team)

and do something similar to player_game_stats.

You may need to fiddle around with the keys of each table. The important thing is the foreign key in a table should contain same columns as the primary key of the table it references.

Upvotes: 1

Related Questions