CODEWITHSUNDEEP
sqlpostgresql
jjuser19jj
jjuser19jj

Reputation: 1699

Postgres constraint and foreign key

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.

Upvotes: 0

Views: 475

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248135

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.

Upvotes: 2

Related Questions