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

Question

That is my code below for creating a table in SQL however I get an error " ORA-02270: no matching unique or primary key for this column-list " Can someone help? Thanks

CREATE TABLE EMP_1 
(
EMP_NUM    VARCHAR(3) PRIMARY KEY,
EMP_LNAME  VARCHAR(15) NOT NULL,
EMP_FNAME  VARCHAR(15) NOT NULL,
EMP_INITIAL CHAR(1),
EMP_HIREDATE DATE, 
JOB_CODE VARCHAR(3),
CONSTRAINT EMP_1_JOB_CODE_FK FOREIGN KEY (JOB_CODE) REFERENCES JOB 
(JOB_CODE)
);

just three question : 1) Do you have a table called `job` ? 2) Do you have a column called `job_code` within `job` table ? 3) Does that column has a unique type key ? — Barbaros Özhan, Sep 19 '19 at 05:19

score 0 · Answer 1 · answered Sep 19 '19 at 04:11

0

please make sure JOB_CODE in JOB table must be a unique key or primary key column.

please alter table JOB and make job_code as primary or unique key .

alter table job add constraints pk_job primary key (job_code)

Then execute your code

answered Sep 19 '19 at 04:11

CMK

40
2
10

Thanks dude , appreciate the input – Mozz Jonas Sep 19 '19 at 12:56

score 0 · Answer 2 · answered Sep 19 '19 at 04:33

The issue is with the following code:

CONSTRAINT EMP_1_JOB_CODE_FK FOREIGN KEY (JOB_CODE) REFERENCES JOB 
(JOB_CODE)

The foreign key always refers to the primary or unique key of some table.

You need to make JOB_CODE as a primary key or unique in the JOB table.

You can check the Primary key or Unique column of any table using the following query:

SELECT
    C.TABLE_NAME,
    C.CONSTRAINT_TYPE,
    CC.COLUMN_NAME
FROM
    USER_CONSTRAINTS C
    JOIN USER_CONS_COLUMNS CC ON ( C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME )
WHERE
    C.CONSTRAINT_TYPE IN (
        'P',
        'U'
    )
    AND C.TABLE_NAME = '<table_name>';

Cheers!!

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

2 Answers2