Postgres constraint and foreign key

Question

Is it possible to enforce a constraint and foreign key only when all values are not null? For example in a polymorphic relation one object would have multiple foreign keys, but often only one is used, so there is a violation. How can I avoid this?

CREATE TABLE IF NOT EXISTS acos (
    id SERIAL PRIMARY KEY,
    created_at timestamp,
    updated_at timestamp,
    owner_id varchar(64) NOT NULL,
    stack_id varchar(64) DEFAULT NULL,
    qac_id varchar(64) DEFAULT NULL,
    rights varchar(1024)[], 
)

Either stack_id or qac_id is set, but never both.

Same goes for the following constraint:

CONSTRAINT name_owner_id UNIQUE 
            (
                name, owner_id
            )

I would like to ignore the constraint when either name or owner_id is null.

Possible duplicate of [Postgresql: Conditionally unique constraint](https://stackoverflow.com/questions/16236365/postgresql-conditionally-unique-constraint) — Dai, Sep 17 '18 at 05:11
I think you might want to redesign your database so that there's a second table that contains the foreign-key columns with all `NOT NULL` types that's referenced from your first table with a `NULL`able foreign key relation. — Dai, Sep 17 '18 at 05:12

score 2 · Accepted Answer · answered Sep 17 '18 at 10:00

Unless I misunderstand you, PostgreSQL already works the way you want by default:

You can have the same entries twice in a UNIQUE constraint as long as one of them is NULL.
If a foreign key column is NULL, the constraint is not enforced, as long as you stick with the default MATCH SIMPLE.
For a condition like “one of two values must be NOT NULL”, you can use a check constraint.

Postgres constraint and foreign key

1 Answers1