CODEWITHSUNDEEP
sqloracle-databaseforeign-keysprimary-key
Josh
Josh

Reputation: 107

Oracle - No matching unique or primary key for this column-list

Ok, sorry if my code is not in the best format. I am getting an error when trying to create the second table. I get this error: ORA-02270: no matching unique or primary key for this column-list. I have read other posts and I am confused to what is wrong with my foreign keys. The foreign keys in E_T are D_ID, and SP_ID. D_id is referencing table d_t and SP_ID is referencing table e_t since it's a it is a unary relationship with E_ID. I believe it is employee ID and supervisor ID. The FK and PK datatypes match. 

CREATE TABLE  D_T
(
     D_ID   INTEGER NOT NULL,
     D_Name VARCHAR(45),

     CONSTRAINT D_T_PK PRIMARY KEY (D_ID)
);

CREATE TABLE E_T
(
     E_ID   INTEGER NOT NULL,
     E_Name VARCHAR(45),
     D_ID   INTEGER,
     Salary INTEGER,
     SP_ID  INTEGER,

     CONSTRAINT E_T_PK PRIMARY KEY (E_ID),
     CONSTRAINT E_T_FK1 FOREIGN KEY (D_ID) REFERENCES D_T(D_ID),
     CONSTRAINT E_T_FK2 FOREIGN KEY (SP_ID) REFERENCES E_T(SP_ID)
);

I would like to note, on the Oracle server -- line 13 and 14 of my script the word "KEY" is not in blue. I am unsure if that is significant.

Upvotes: 1

Views: 106

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You are referencing the wrong column for the self-reference. So the second table should look more like:

CREATE TABLE E_T (
 E_ID    INTEGER NOT NULL PRIMARY KEY,
 E_Name    VARCHAR(45),
 D_ID    INTEGER,
 Salary    INTEGER,
 SP_ID    INTEGER,
 CONSTRAINT E_T_FK1 FOREIGN KEY (D_ID) REFERENCES D_T(D_ID),
 CONSTRAINT E_T_FK2 FOREIGN KEY (SP_ID) REFERENCES E_T(E_ID)
-------------------------------------------------------^
)

Upvotes: 2

Related Questions