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 — Dai
– Dai, Commented Sep 17, 2018 at 5: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 NULLable foreign key relation. — Dai
– Dai, Commented Sep 17, 2018 at 5:12

Laurenz Albe · Accepted Answer · 2018-09-17 10:00:36Z

2

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.

answered Sep 17, 2018 at 10:00

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres constraint and foreign key

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related