Re: foreign keys between 2 tables - newbie question

randp_at_kos.net wrote:
> Hi,
> I have two tables :
>
> 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.
>
> How do I overcome this?
>
> Is there a general rule that 2 tables can't point to each other for
> Foreign Keys.
>
> thanks,
> Roshan

The best practice is to NEVER create any constraint when creating a table (with the sole exception being NOT NULL).

Use ALTER TABLE to create your constraints and you can do anything that is logically possible.

http://www.psoug.org
click on Constraints

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)