Constructing Circularly Referenced Tables

Question

I'm attempting to construct two tables in Oracle, A and B; each of these tables reference each other. In my script, I have the foreign key constraints 'in-line' of the create table statements. Of course, both the create table A and create table B are failing because neither table has been created yet.

Is it possible to keep the foreign key constraints in-line, or must they be added only after the table(s) have already been created by means of an alter table statement?

Answer 1

You must add the foreign key constraints after the tables have been created, using: alter table table_name add constraint constraint_name <constraint clause>;

There is no other way.

Note that, due to your table design, you may have to create the constraints with the initially deferred deferrable clause. This forces Oracle to defer constraint checking until commit time & may be necessary if you cannot use an INSERT ALL statement and need to insert dependant rows into the two tables with separate statements.