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.

Answer 1

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.

Answer 2

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.