PostgreSQL – Force Create Foreign Key Without Reference

foreign keypostgresql

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?

Best Answer

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

Related Solutions

Sql-server – SQL Server 2005, Foreign key check against part of a table

No this isn't possible.

I'd probably create a separate table categoriesWithArticles that holds the relevant categoryids (instead of having the flag) and have the FK reference that table instead.

Another more convoluted way to enforce it and keep the column would be

CREATE TABLE categories
(
categoryId int primary key,
hasarticles bit not null,
unique(categoryId, hasarticles)
)

CREATE TABLE articles
(
categoryId int,
hasarticles as cast (1 as bit) persisted,
FOREIGN KEY (categoryId, hasarticles) REFERENCES categories(categoryId, hasarticles)
)

But this requires the creation of a technically redundant unique constraint as well as the additional persisted column in the articles table.

Postgresql – use `on delete set null` on a compound foreign key which contains a primary key column

You can't use on delete set null if any of the foreign key columns don't accept null:

create schema stack;
set search_path=stack;
--
create table t1( foo integer
               , bar integer
               , primary key(foo,bar) );
--
create table t2( foo integer
               , baz integer
               , bar integer
               , primary key (foo,baz)
               , foreign key (foo,bar) references t1 on delete set null );
--
insert into t1 values (1,1);
insert into t2 values (1,1,1);
delete from t1;
/*
ERROR:  null value in column "foo" violates not-null constraint
DETAIL:  Failing row contains (null, 1, null).
CONTEXT:  SQL statement "UPDATE ONLY "postgres"."t2" SET "foo" = NULL, "bar" = NULL WHERE $1 OPERATOR(pg_catalog.=) "foo" AND $2 OPERATOR(pg_catalog.=) "bar""
*/
--
drop schema stack cascade;

And the same is true for on delete set default. I don't know of any simple way round this, and I'm not sure I agree that it is the most sensible behaviour, I think a better choice for on delete set null would be to set the nullable columns to null if there are both.

Best Answer

Related Solutions

Sql-server – SQL Server 2005, Foreign key check against part of a table

Postgresql – use `on delete set null` on a compound foreign key which contains a primary key column

Related Question