Constraints (Part 5) - ORA-02270, ORA-02249, ORA-02291 and ORA-02292

Go to part 4

This example, tested on an Oracle 9 database, starts to look at foreign key constraints. First create a department table. Each row has a department number and description:

SQL> create table dept
  2  (dept_no   varchar2(4),
  3   dept_desc varchar2(10))
  4  /

Table created.

SQL> 

Next, create an employee table. Each row has an employee number, name and department number. A foreign key constraint checks that the department number is already present in the department table:

SQL> create table emp
  2  (emp_no      varchar2(4),
  3  emp_name     varchar2(10),
  4  emp_dept_no  varchar2(4)
  5  constraint fk_dept_no
  6  references dept(dept_no))
  7  /
references dept(dept_no))
                *
ERROR at line 6:
ORA-02270: no matching unique or primary key for this
column-list

SQL>

That fails because the department number in the department table does not have to be unique. Add a constraint to ensure it is then try again:

SQL> alter table dept add constraint dept_no_pk
  2  unique(dept_no)
  3  /

Table altered.

SQL> create table emp
  2  (emp_no      varchar2(4),
  3  emp_name     varchar2(10),
  4  emp_dept_no  varchar2(4),
  5  constraint fk_dept_no
  6  foreign key (emp_dept_no)
  7  references dept(dept_no))
  8  /

Table created.

SQL>

So far so good. Now see what happens if you try to drop the department table. Note that both tables are empty at this point:

SQL> drop table dept
  2  /
drop table dept
          *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by
foreign keys

SQL> 

That fails too because the department table is now required to validate changes to the employee table. Try to add a row to the employee table:

SQL> insert into emp values ('E001','Andrew','D001')
  2  /
insert into emp values ('E001','Andrew','D001')
*
ERROR at line 1:
ORA-02291: integrity constraint (ORACLE.FK_DEPT_NO)
violated - parent key not found

SQL>

The insert is rejected because the department number has not been added to the department table yet. Add a couple of entries in the department table (including the one needed for the employee row above) and have another go:  

SQL> insert into dept values ('D001','IT')
  2  /

1 row created.

SQL> insert into dept values ('D002','Sales')
  2  /

1 row created.

SQL> insert into emp values ('E001','Andrew','D001')
  2  /

1 row created.

SQL>

That worked. Now try to delete rows from the department table:

SQL> select * from dept
  2  /

DEPT DEPT_DESC
---- ----------
D001 IT
D002 Sales

SQL> delete dept
  2  /
delete dept
*
ERROR at line 1:
ORA-02292: integrity constraint (ORACLE.FK_DEPT_NO)
violated - child record found

SQL> select * from dept
  2  /

DEPT DEPT_DESC
---- ----------
D001 IT
D002 Sales

SQL>

That didn't work either because D001 already has one employee in the emp table. Note that D002 was not deleted either, even though it has no employees. Finally, drop both tables. If you do the emp table first, there is no problem:

SQL> drop table emp
  2  /

Table dropped.

SQL> drop table dept
  2  /

Table dropped.

SQL>

Go to part 6