Question about using foreign key constraints in Oracle

by Apoorva

From Apoorva - I am doing SQL / PLSQL course for Oracle. Right now, I am doing SQL and have came across a problem. Following is the SQL statement :


CREATE TABLE title_copy (
copy_id number(10),
title_id number(10),
status varchar2(15)
CONSTRAINT title_copy_status_nn NOT NULL,
CONSTRAINT title_copy_status_chk CHECK(status IN ('AVAILABLE', 'DESTROYED', 'RENTED', 'RESERVED')),
CONSTRAINT title_copy_titleid_fk FOREIGN KEY(title_id) REFERENCES title_copy(copy_id),
CONSTRAINT title_copy_copyid_titleid_pk PRIMARY KEY(copy_id,title_id));


I am getting following error from Oracle:
REFERENCES title_copy(copy_id),
*
ERROR at line 12: ORA-02270: no matching unique or primary key for this column-list Please help...


Before we look at the specific problem, let's just review the subject of foreign key constraints briefly. The purpose of the foreign key constraint is to ensure that no orphan records are created in our Oracle database - i.e. every child record created in the table with a foreign key constraint must have a parent record in the referenced table.

Let's take the classic example of departments and employees. Let's assume we have a table called DEPARTMENT with columns department_id and department_name to hold details about the different departments in the organisation.

Let's suppose also that we have another table called EMPLOYEE with columns employee_id, department_id and employee_name to hold employee details.

To ensure that we don't, in our database, link employees to a nonexistent department we would create a foreign key on the department_id column in the employee table to reference the department_id column in the department table as follows:-

CREATE TABLE employee (
employee_id NUMBER(10) PRIMARY KEY,
department_id NUMBER(10) NOT NULL,
employee_name VARCHAR2(60) NOT NULL,
CONSTRAINT emp_fk FOREIGN KEY(department_id) REFERENCES department(department_id));


Now that we've look at the big picture, so to speak, we can look at the specific problem which is due to the fact that a foreign key must refer to either a unique key or a primary key in the referenced table. In this case, title_copy.copy_id is not the primary key nor unique, so the table creation statement fails with the Oracle error
ORA-02270: no matching unique or primary key for this column-list
.

The way to solve this problem is to add the primary key constraint to the copy_id column in the table creation statement like so:-

CREATE TABLE title_copy (
copy_id number(10) PRIMARY KEY,
title_id number(10),
status varchar2(15)
CONSTRAINT title_copy_status_nn NOT NULL,
CONSTRAINT title_copy_status_chk CHECK(status IN ('AVAILABLE', 'DESTROYED', 'RENTED', 'RESERVED')),
CONSTRAINT title_copy_titleid_fk FOREIGN KEY(title_id) REFERENCES title_copy(copy_id),
CONSTRAINT title_copy_copyid_titleid_pk PRIMARY KEY(copy_id,title_id));


This statement will create the table without any errors but it does lead to another problem - we can't insert any records into the table! Running an insert statement such as

insert into title_copy values (1,2,'AVAILABLE');

produces the Oracle error:

ORA-02291: integrity constraint (HR.TITLE_COPY_TITLEID_FK) violated - parent key not found.

In other words the parent of the record we are inserting was not found in the table. This is because the table was empty which as this is the first insert into the table gives us a slight problem.

Fortunately Oracle provides us with several ways around this problem:
  • we could forget about using a foreign key constraint at all which would leave us open to the problem of orphan records
  • or we could disable or drop the constraint until after we have created the first record
However even with option 2 we would still have a problem as Oracle normally checks every record to ensure that the constraint holds true when teh constraint is re-enabled or re-created. We would get this error:

ORA-02298: cannot validate (HR.TITLE_COPY_TITLEID_FK) - parent keys not found

as soon as we re-enabled or re-created the constraint unless the title_id was the same as the copy_id. The way to avoid this is to enable the constraint without validating the existing data by using the Oracle SQL statement:

alter TABLE title_copy enable novalidate constraint title_copy_titleid_fk .

That way the first record won't be validated but any future inserts or updates on the table will be validated against the constraint.

You might also find this question and answer useful Please tell me about constraints in Oracle.

If you're looking for more formal training on Oracle, why not take a training course with one of our partners? See our Oracle training page for more details.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.