Create two tables whose foreign keys reference each other

Question

In Oracle SQL, SQL Developer: I am trying to create two tables, each which has a foreign key that references the other table's primary key.

Using my logic, I cannot set the foreign key references because the other table does not yet exist.

Here's a general idea of how I'm structuring it:

CREATE TABLE table1 
(
    column1 datatype PRIMARY KEY NOT NULL,
    column2 datatype NOT NULL,
    column3 datatype NOT NULL,

    CONSTRAINT fk_keyname 
        FOREIGN KEY (colmn3)
        REFERENCES otherTable (column3)
);

CREATE TABLE table2 
(
    column1 datatype PRIMARY KEY NOT NULL,
    column2 datatype NOT NULL,
    column3 datatype NOT NULL,

    CONSTRAINT fk_keyname2
        FOREIGN KEY (colmn3)
        REFERENCES otherTable2 (column3)
);

I'm getting an error

ORA-00942: table or view does not exist

I have fixed this before by creating the parent table first, but since they both reference each other I'm at a loss as what I need to do here because the MUST REFERENCE EACH OTHER in this particular case.

Create the tables with all the columns, but without the FKs. Then add the FKs using `ALTER TABLE`. — The Impaler, Oct 08 '19 at 01:00

The Impaler · Accepted Answer · 2019-10-08T01:11:50.857

You can create the tables first, then the FKs. For example:

create table table1 (
  column1 int primary key not null,
  column2 int not null,
  column3 int not null
);

create table table2 (
  column1 int primary key not null,
  column2 int not null,
  column3 int not null,
  constraint fk2
    foreign key (column3)
    references table1 (column1)
);

alter table table1 add constraint fk1
   foreign key (column3)
   references table1 (column1);

Even though the tables will be created you won't be able to insert data in them since the constraint will prevent you create a row that doesn't point to the other [non existent] row. In order to insert data you'll need to create the constraint as "deferrable". Here's the improved SQL script:

create table table1 (
  column1 int primary key not null,
  column2 int not null,
  column3 int not null
);

create table table2 (
  column1 int primary key not null,
  column2 int not null,
  column3 int not null,
  constraint fk2
    foreign key (column3)
    references table1 (column1) deferrable initially deferred
);

alter table table1 add constraint fk1
   foreign key (column3)
   references table1 (column1) deferrable initially deferred;

Now, make sure you insert the rows of all involved tables between the boundaries of a transaction. The constraints will now be checked at the end of the transaction only and not on every inserted/modified/deleted row.

In the question, both foreign keys reference `column3` in the other table. In which case `column3` would need to be unique in both tables so that there is a constraint to verify against. — Justin Cave, Oct 08 '19 at 01:16

score 4 · Answer 2 · answered Oct 08 '19 at 00:48

From a technical perspective, it would be possible to create the first table without the foreign key constraint, then create the second table (with the foreign key), and finally add the foreign key constraint to the first table.

But the problem that you are meeting does indicate a design problem. It also prefigures the issue that will deal with when you will try to populate the tables: because of the cross foreign keys, you will not be able INSERT records in the tables, unless you do some complicated things like temporarily disabling one of the constraint, inserting in both tables, updating the first table, then enable the constraint.

Your question does not give enough context for me to provide a suggestion for an alternative design, but, based on experience, a better solution exists for sure. It can involve having a single table instead of two (if you are dealing with a 1-1 relationship), or having a third table that acts like a bridge table if this is a N-M relationship.

It's actually possible to insert in those tables in Oracle (as well as PostgreSQL) since these engines implement the *standard* SQL feature of constraint deferrability. — The Impaler, Oct 08 '19 at 01:03
@TheImpaler: I agree, and I like your answer, although I still tend to believe that this kind of setup indicates a design problem. Upvoted anyway. — GMB, Oct 10 '19 at 00:07
Yeah, I agree that most commonly this denotes a design problem. I would add, though, there are actual, few, genuine cases where this is needed. — The Impaler, Oct 10 '19 at 20:28

Create two tables whose foreign keys reference each other

2 Answers2

Linked