1

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?

1
  • No. The integrity system doesn't care about your guarantees and assurances.
    – Akina
    Jan 31, 2020 at 4:34

2 Answers 2

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

1
  • I guess I'll have to deal with it like this for now.
    – bli00
    Jan 31, 2020 at 22:49
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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