Create table with foreign key to an other table created below in sql file

Question

My problem is that i have two tables with each table having a foreign key to the other table. Each time , i execute the SQL file containing the creation of the two tables, it gives me an error that he doesn't find the other table. I'm working with sqlplus to execute the sql file. Here's an example of SQL file i tried with :

create table A(
Age number(3),
name number(3) constraint A_FK references B(name))
/
create table B(
Age number(3) constraint B_FK references A(Age),
name number(3))

And even if i reverse the order, it gives the same error. Thanks for help.

Answer 1

The table column(s) that is referred by a foreign key must exist at the time when the constraint is created. Since you have some kind of cyclic reference between the tables, you need to do this in three steps:

• first create one table without the foreign key

• create the second table (with its foreign key)

• finally add the foreign key to the first table with an alter table statement

You also need the referred column to have a unique or primary key constraint set up, otherwise you would get error ORA-02270: no matching unique or primary key for this column-list.

create table A(
    age number(3) primary key,
    name number(3) 
);

create table B(
    age number(3) constraint B_FK references A(Age),
    name number(3) primary key
);

alter table A add constraint A_FK foreign key (name) references B(name);

Demo on DB Fiddle

Side note: I am quite suspicious about your sample structure, but this could be because your oversimplified it in the question.