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_id
s 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.