SQL: Unique constraint when column is a certain value
postgresql, sql
Solution
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');
Problem
``` 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.