Force create foreign key even if reference does not exist

Question

I have two tables foo and bar, bar has column a that references foo.id as a foreign key. When creating table bar before foo, the error column "a" referenced in foreign key constraint does not exist is given. This is obvious and intended. However, the creation of these two tables are handled by two different entities that of which I cannot control. It's guaranteed that no data will be added to either table until both are successfully created, but the creation itself may happen out-of-order. Is there a way to force the creation of bar even though foo does not exist?

Answer 1

As a quick fix. you can add a semaphore that prevents the creation of bar before foo exists. Pseudocode:

WHILE true:
    n = (select count(1) 
         from information_schema.tables 
         where table_name = 'foo')
    if n == 1:
        break
    sleep 1

create table bar ...  

In the long run, consider how you should handle dependencies between different parts of the system, that are created independently of each other

Answer 2

You can create the foreign key in a separate step:

CREATE TABLE bar (
   id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   a  bigint NOT NULL
);

CREATE TABLE foo (
   id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY
);

ALTER TABLE bar ADD FOREIGN KEY (a) REFERENCES foo (id);

The only condition is that the ALTER TABLE has to be executed after the other two statements, but the order in which the CREATE TABLE statements are issued is irrelevant.

That's the best you can do.