Re: foreign keys between 2 tables

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Wed, 18 May 2005 21:23:35 +0100
Message-ID: <hg6n811sq14pbkigdivkt54204l3n647aj@4ax.com>

On 18 May 2005 12:01:48 -0700, randp_at_kos.net wrote:

>Employee
>---------
>emp_id PK
>f_name
>l_name
>dept_id FK (to department.dept_id)
>
>Department
>------------
>dept_id PK
>name
>manager_id FK (to employee.emp_id)
>
>
>I guess, I can't create these tables, because when I create the
>employee table the foreign key breaks down. So I first created the
>employee table without the FK and then the department table and then
>did an "alter table employee" to add FK.
>
>But, when inserting values I have the same issue. ie: referential
>integrity test fails.

Look up "deferrable constraints", since you have a chicken-and-egg situation here when you're trying to insert a new department - creating the constraints after populating the tables works for any current data but for new departments this may be of some use:

SQL> create table employee (

  2      emp_id number not null,
  3      dept_id number not null,
  4      constraint employee_pk primary key (emp_id)

5 );

Table created.

SQL> create table department (

  2      dept_id number not null,
  3      manager_id number not null,
  4      constraint department_pk primary key (dept_id)

5 );

Table created.

SQL> alter table employee add constraint employee_fk1

  2      foreign key (dept_id)
  3      references department (dept_id)
  4      deferrable;

Table altered.

SQL> alter table department add constraint department_fk1

  2      foreign key (manager_id)
  3      references employee (emp_id)
  4      deferrable;

Table altered.

SQL> insert into employee values (1, 1); insert into employee values (1, 1)
*
ERROR at line 1:
ORA-02291: integrity constraint (TEST.EMPLOYEE_FK1) violated - parent key not found

SQL> insert into department values (1, 1); insert into department values (1, 1)
*
ERROR at line 1:
ORA-02291: integrity constraint (TEST.DEPARTMENT_FK1) violated - parent key not found

OK, not getting very far with putting data in because of the cyclic foreign key constraints - so set the constraints to deferred - this allows temporary violations, but they've got to match up by the time you commit ***

SQL> set constraints all deferred;

Constraint set.

SQL> insert into department values (1, 1);

1 row created.

SQL> insert into employee values (1, 1);

1 row created.

SQL> set constraints all immediate;

Constraint set.

SQL> commit;

Commit complete.

SQL> select * from employee;

EMP_ID DEPT_ID
---------- ----------

1 1

SQL> select * from department;

DEPT_ID MANAGER_ID
---------- ----------

1 1

A schema that forces you to use deferred constraints to insert new rows could be a bit fiddly to work with, though.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Received on Wed May 18 2005 - 15:23:35 CDT

Re: foreign keys between 2 tables - newbie question