Skip to content
Advertisement

Can’t enable a foreign key in ORACLE SQL

So I have a disabled foreign key (Manager_ID in the table Employees) and I wanted to enable it, but I get this error:

ORA-02298: cannot validate (my_username.EMP_MANAGER_FK) – parent keys not found

And this is the code:

ALTER TABLE Employees ENABLE CONSTRAINT EMP_MANAGER_FK;

Here’s how the table was created:

 CREATE TABLE  "EMPLOYEES" 
   (    "EMPLOYEE_ID" NUMBER(6,0), 
    "FIRST_NAME" VARCHAR2(20), 
    "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE, 
    "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE, 
    "PHONE_NUMBER" VARCHAR2(20), 
    "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE, 
    "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE, 
    "SALARY" NUMBER(8,2), 
    "COMMISSION_PCT" NUMBER(2,2), 
    "MANAGER_ID" NUMBER(6,0), 
    "DEPARTMENT_ID" NUMBER(4,0), 
    "BONUS" VARCHAR2(5), 
     CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE, 
     CONSTRAINT "EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
   USING INDEX  ENABLE, 
     CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
   USING INDEX  ENABLE
    )
   /
   ALTER TABLE  "EMPLOYEES" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY 
    ("DEPARTMENT_ID")
      REFERENCES  "DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
    /
ALTER TABLE  "EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
      REFERENCES  "JOBS" ("JOB_ID") ENABLE
/
ALTER TABLE  "EMPLOYEES" ADD CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
      REFERENCES  "EMPLOYEES" ("EMPLOYEE_ID") DISABLE
/

CREATE INDEX  "EMP_DEPARTMENT_IX" ON  "EMPLOYEES" ("DEPARTMENT_ID")
/

CREATE INDEX  "EMP_JOB_IX" ON  "EMPLOYEES" ("JOB_ID")
/

CREATE INDEX  "EMP_MANAGER_IX" ON  "EMPLOYEES" ("MANAGER_ID")
/

CREATE INDEX  "EMP_NAME_IX" ON  "EMPLOYEES" ("LAST_NAME", "FIRST_NAME")
/

Advertisement

Answer

There are values in the column that are invalid from the foreign key’s perspective.

Assuming that you have a self-referencing foreign key like:

create table employees (
    employee_id int primary key,
    manager_id  int references employees(employee_id)
);

Then the error message indicates that there are manager_ids that do not exists in column employee_id. You can exhibit the offending rows with a query like:

select manager_id
from employees e
where not exists (select 1 from employees e1 where e1.employee_id = e.manager_id)

You need to fix this before you can enable the foreign key.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement