CODEWITHSUNDEEP
oracle10g
Jackin Millins
Jackin Millins

Reputation: 1

I want to resolve this error "no matching unique or primary key for this column-list"

CREATE TABLE EMP(EMP_ID NUMBER(5), F_NAME VARCHAR2(20) NOT NULL, L_NAME VARCHAR2(20) NOT NULL, 
EMAIL VARCHAR2(20) NOT NULL, PHONE_NO NUMBER(10) NOT NULL, HIRE_DATE DATE NOT NULL,
JOB_ID NUMBER(5) NOT NULL, SALARY NUMBER(5) NOT NULL, COMMISSION_PCT NUMBER(5), 
MANAGER_ID NUMBER(5) NOT NULL, DEPT_ID NUMBER(5) NOT NULL, PRIMARY KEY (EMP_ID), 
CONSTRAINT FK_ED FOREIGN KEY (DEPT_ID) REFERENCES DEPT (DEPT_ID), 
CONSTRAINT FK_EDM FOREIGN KEY (MANAGER_ID) REFERENCES DEPT (MANAGER_ID));

When I add this line it is throwing the error ORA-02270.
I have already created the table DEPT which includes dept_id as primary key and manager_id as not null. But when I use foreign key as dept_id there is no error but for manager_id it is throwing the error.

Please help me resolve this.

Upvotes: 0

Views: 20

Answers (1)

Mihai Pasca
Mihai Pasca

Reputation: 54

dept_id is a primary key in DEPT, so you can have a foreign key for it in EMP.

manager_id is just a not-null column in DEPT. As it is not part of the primary key in DEPT, you cannot have a foreign key for it in EMP.

Upvotes: 1

Related Questions