1

I have created two tables, the parent in this case being "department" and "employee" being child.

CREATE TABLE department  
(  
  department_id NUMBER(4) CONSTRAINT department_id_not_null NOT NULL,  
  department_name VARCHAR2(30) CONSTRAINT department_name_not_null NOT NULL,  
  location_id NUMBER(4),  
  manager_id NUMBER(6),  
  CONSTRAINT manager_unique UNIQUE (manager_id),  
  CONSTRAINT department_pk PRIMARY KEY (department_id)  
);  


CREATE TABLE employee  
(  
  employee_id NUMBER(6)  CONSTRAINT employee_id_not_null NOT NULL,  
  first_name VARCHAR2(20),  
  last_name VARCHAR2(25) CONSTRAINT last_name_not_null NOT NULL,        
  email VARCHAR2(25) CONSTRAINT email_not_null NOT NULL,  
  phone_number VARCHAR2(20),  
  hire_date date CONSTRAINT hire_date_not_null NOT NULL,  
  job_id VARCHAR2(10) CONSTRAINT job_id_not_null NOT NULL,  
  salary NUMBER (8,2),        
  commission_pct NUMBER(2,2),  
  manager_id NUMBER(6),  
  department_id NUMBER (4),  
  CONSTRAINT manager_unique FOREIGN KEY (manager_id) REFERENCES department (manager_id),  
  CONSTRAINT department_fk FOREIGN KEY (department_id) REFERENCES department (department_id),  
  CONSTRAINT employee_pk PRIMARY KEY (employee_id)  
);  

The problem is that whenever I try to create the second table "employee" it gives me this error:

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

I haven't been sure about how to reference the unique key in the child table so I also tried using the UNIQUE constraint instead of the FOREIGN KEY constraint but to no avail. The table is created fine without the unique key reference though.

1

Thanks to ypercube,

I had to drop the parent table "department", recreate it and then use ALTER TABLE employee ADD CONSTRAINT fk_manager_unique FOREIGN KEY (manager_id) REFERENCES department (manager_id); everything worked just fine and the foreign key reference to the unique manager_id key were finally created. Note that I had to change the name of the constraint "manager_unique" in the second table because of duplication issues.

Thanks again!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.