Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
3  
Why do you need such a setup? Sounds terrible... –  dezso Jul 3 '12 at 17:32
3  
First create the tables with no constraints and then add the constraints. –  srini.venigalla Jul 3 '12 at 19:26
2  
If you post why you want this, we may come up with a more RDBMS-friendly implementation. –  AlexKuznetsov Jul 3 '12 at 21:55
    
@Isaac: You either follow the hard road that Phil shows (with the deferred and deferrable constraints) or redesign the tables to remove circular paths in FKs. –  ypercube Jul 4 '12 at 14:30
1  
A common problem that leads to this (circular) design is the "Something has many Items" and every "Something has a default (first, best, you name it) Item". If that's the case, see this similar question: In SQL, is it OK for two tables to refer to each other? and some approaches to dealing with it. (note that it is for MySQL, so only 3 lines are devoted to the deferrable constraints solution) –  ypercube Jul 4 '12 at 14:56
add comment

1 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.