SQL: Unique constraint when column is a certain value

Question

CREATE TABLE foo ( dt AS DATE NOT NULL, type AS TEXT NOT NULL, CONSTRAINT unique_dt_type UNIQUE(dt,type) -- check constraint(?) )

Having a brain-dud when trying to think of the right syntax to create a unique constraint when only a certain condition exists.

Given, type can have values A-F, there can only be one A per date, but there can be multiple B-F. Example of good table:

2010-01-02 | 'A' -- only one 2010-01-02 | 'B' -- can have multiple 2010-01-02 | 'B' 2010-01-02 | 'B' 2010-01-02 | 'C' -- can have multiple 2013-01-02 | 'A' -- only one 2010-01-02 | 'B' -- can have multiple 2010-01-02 | 'B' 2013-01-02 | 'F' -- can have multiple 2013-01-02 | 'F'

Tried reading check/unique syntax but there weren't any examples. CHECK came close but only limited it to a range and wasn't used in conjunction with a UNIQUE scenario. Also tried searching, but my search skills are either not up to par, or there aren't any similar questions.

@Houari: that sounds promising, do you have a quick example? I did find some results when I searched for sql conditional unique, but no success (they were dated) — vol7ron
– vol7ron, Commented Apr 8, 2013 at 20:57
I might have found a solution in the question stated here: stackoverflow.com/q/987099/183181 , if where is allowed during index creation — vol7ron
– vol7ron, Commented Apr 8, 2013 at 20:59
Yep, look up "partial indexes" in the Postgres manual. You can do Create Unique Index ... Where ..., and it will be enforced like a constraint. (I'm on a phone, else I'd expand that into an answer) — IMSoP
– IMSoP, Commented Apr 8, 2013 at 21:05
Ha, we all converged on the same answer within a few seconds of each other. It wasn't there when I was posting. — RThomas
– RThomas, Commented Apr 8, 2013 at 21:08

RThomas · Accepted Answer · 2019-10-05 17:05:19Z

PostgreSQL can address your needs via it's "Partial Index" feature. In practice this is accomplished by adding a where clause to the create index statement.

Sample:

CREATE INDEX my_partial_ix ON my_sample_table (my_sample_field) WHERE (my_sample_field = 'rows to index');

Take a look here: http://www.postgresql.org/docs/current/interactive/indexes-partial.html

Pay particular attention to the section Example 11-3. Setting up a Partial Unique Index. It gives an example that lines up well with your stated objective.

CREATE UNIQUE INDEX my_partial_ix ON my_sample_table (my_sample_field) WHERE NOT (my_sample_field = 'duplicates ok');

Houari · Accepted Answer · 2013-04-08 21:13:31Z

Using a trigger:

CREATE OR REPLACE FUNCTION "CheckConstraint"() RETURNS trigger AS $BODY$declare already_exists boolean; begin if new.foo_type='A' then select count(*) >0 from foo where foo_type='A' and dt=new.dt INTO already_exists; if already_exists then raise exception 'date % already have an A', new.dt; end if; end if; return new; end;$BODY$ LANGUAGE plpgsql VOLATILE COST 100;

Way too complicated. This won't scale and using a partial index is much faster and more robust.
Plus, the index automatically serves to speed up matching queries. Only tiny drawback: the index needs some disk space.

Collectives™ on Stack Overflow

SQL: Unique constraint when column is a certain value

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related