HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Constraint to prevent 'duplicates' only when column > 0

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
preventcolumnconstraintwhenonlyduplicates

Problem

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.

Solution

Add a conditional unique index

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


dbfiddle here

Code Snippets

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

Context

StackExchange Database Administrators Q#166477, answer score: 6

Revisions (0)

No revisions yet.