ORA-00904: "value_name": invalid identifier

Question

I am trying to create this Oracle SQL table with the following parameters:

SQL Table

This is my current SQL query:

CREATE TABLE Match
(
    MId INTEGER,
    TId INTEGER CONSTRAINT tid REFERENCES Tournament(tid),
    Player1 INTEGER CONSTRAINT ply1 REFERENCES Player(ply1),
    Player2 INTEGER CONSTRAINT ply2 REFERENCES Player(ply2),
    MatchDt DATE NOT NULL,
    Winner INTEGER CONSTRAINT win REFERENCES Player(win),
    Score VARCHAR2(30) NOT NULL,

    CONSTRAINT marks_cid_pk PRIMARY KEY(MId, TId),
    CONSTRAINT columns_cannot_equal CHECK (Player1 <> Player2)
);

I get this error

ORA-00904: "PLY1": invalid identifier

Not sure why it is wrong. Any help is appreciated.

I have tried to rename the values and reorder it but to no avail

Answer 1

You're referencing a non-existent column (not JUST a column, but a primary key column (could be unique key as well, but most usually it is a primary key) in player table.

Missing tables (otherwise, match can't reference them):

SQL> CREATE TABLE player
  2  (
  3     ply   INTEGER PRIMARY KEY
  4  );

Table created.

SQL> CREATE TABLE tournament
  2  (
  3     tid   INTEGER PRIMARY KEY
  4  );

Table created.

Your statement, fixed:

SQL> CREATE TABLE match
  2  (
  3     mid       INTEGER,
  4     tid       INTEGER CONSTRAINT tid  REFERENCES tournament (tid),
  5     player1   INTEGER CONSTRAINT ply1 REFERENCES player (ply),    -- ply1
  6     player2   INTEGER CONSTRAINT ply2 REFERENCES player (ply),    -- ply2
  7     matchdt   DATE    NOT NULL,
  8     winner    INTEGER CONSTRAINT win  REFERENCES player (ply),    -- win
  9     score     VARCHAR2 (30) NOT NULL,
 10     --
 11     CONSTRAINT marks_cid_pk PRIMARY KEY (mid, tid),
 12     CONSTRAINT columns_cannot_equal CHECK (player1 <> player2)
 13  );

Table created.

SQL>