Postgresql – Constraint to prevent ‘duplicates’ only when column > 0

constraintpostgresqlpostgresql-9.6unique-constraint

I have a table with a fk column that should be unique when another int column is > 0. If that sentence doesn't make sense, here is an example of valid rows:

CREATE TABLE foo
AS
  SELECT fk,int
  FROM ( VALUES
    ( 1, -12 ),
    ( 1,  20 ),
    ( 1,   0 )
  ) AS (fk, int);

Adding a new row to the table with a fk value of 1 and a int column that is > 0 would be invalid.

Is there a way to do this with a constraint or index? I feel like an exclude constraint nearly gets me there, but I couldn't figure out a way to get it to work.

I'm using Postgres 9.6.2.

Best Answer

Add a conditional unique index

CREATE UNIQUE INDEX IndexName ON foo (fk) WHERE (int>0);

dbfiddle here

Proper solution

The core of the problem is the data model. In a normalized schema, you wouldn't store name and email redundantly. Could look like this:

CREATE TABLE name (
  name_id          SERIAL PRIMARY KEY,
  name             TEXT NOT NULL,
  email            TEXT NOT NULL,
  verified         BOOLEAN NOT NULL DEFAULT FALSE,
  UNIQUE (name, email)
);

CREATE TABLE address (
  address_id       SERIAL PRIMARY KEY,
  name_id          INT REFERENCES name(name_id)
  ...
);

If not-yet verified names should be allowed to break the UNIQUE constraint, you can replace it with a partial UNIQUE INDEX (much like you had in mind):

CREATE UNIQUE INDEX name_verified_idx ON name(name, email) WHERE verified;

Work with what you have

While stuck with your unfortunate design, the solution you already found yourself fits your requirements perfectly. A FOREIGN KEY with the default MATCH SIMPLE behavior matches the

partial index syntax with a clause like WHERE verified_name_id IS NOT NULL.

Quoting the manual:

MATCH SIMPLE allows any of the foreign key columns to be null; if any of them are null, the row is not required to have a match in the referenced table.

A (somewhat less reliable and more expensive) alternative would be a trigger on INSERT / UPDATE in address and trigger(s) on INSERT / UPDATE / DELETE in verified_name.

Postgresql – Why do composite foreign keys need a separate unique constraint

It's a limitation of the DBMS - in all of them as far as I know. And not only when adding a column but also when rearranging columns. If we have a UNIQUE constraint on (a1, a2), we can't add a FOREIGN KEY that REFERENCES (a2, a1) unless there is a unique constraint on that (a2, a1) that essentially is redundant.

It wouldn't be terrifically difficult to add this as a feature:

When there is a UNIQUE constraint on (a), then any (a, b, c, ..., z) or (b,c, ...a, ...z) combination is also guaranteed UNIQUE.

or the generalization:

When there is a UNIQUE constraint on (a1, a2, ..., aN), then any (a1, a2, ..., aN, b1, b2, ..., bM) combination or any rearrangement is also guaranteed UNIQUE.

It seems that it hasn't been asked or it hasn't been considered high enough priority to be implemented.

You can always make a request - in the respective channel - for the feature to be implemented. Or even implement it yourself, if the DBMS is open source, like Postgres.

Best Answer

Related Solutions

Postgresql – Two-column foreign key constraint only when third column is NOT NULL

Proper solution

Work with what you have

Postgresql – Why do composite foreign keys need a separate unique constraint

Related Question