PostgreSQL: Conditional unique constraint

Question

Given:

----------------------------------
      vin     | driver | is_owner
--------------+--------+----------
 231431cxzv87 | bob    | true
 231431cxzv87 | jeff   | false
 231431cxzv87 | greg   | false
 32342klj234s | jeff   | true

Is there a way to add a constraint so that there is only one owner per vin?

Edit: I found this question.
Is adding a partial unique index meant to suit this purpose?

Yes, a partial unique index is what you need.

Frank Heikens
– Frank Heikens

2012-06-07 19:35:16 +00:00
Commented Jun 7, 2012 at 19:35 — Frank Heikens
– Frank Heikens, Commented Jun 7, 2012 at 19:35

kworr · Accepted Answer · 2012-06-07 19:57:52Z

15

Yes partial index is your choice.

create unique index unique__vin on table (vin) where is_owner;

Here index covers only rows where is_owner is true and withing this rows vin should be unique.

answered Jun 7, 2012 at 19:57

kworr

3,6941 gold badge23 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

vol7ron Over a year ago

Any idea why there isn't a constraint/check convention, versus using an index?

user330315 Over a year ago

@vol7ron: probably because a constraint is (by definition) always applied to all rows in a table.

kworr Over a year ago

AFAIK adding any constraint would require querying table when checking for vin uniqueness. Querying table would result in index scan. You will still use an index however I think each check would cost you more points.

vol7ron Over a year ago

@a_horse_with_no_name: but the condition would apply to all rows of the table :) there can only be one unique(a,b) where b is true, which is compared to each record. Unique constraints already compare the current tuple to others, to make sure the values do not already exist, to make sure it really is unique - how this is done varies, but is probably through a hash lookup. A partial index is doing the same thing, but has one added condition

Collectives™ on Stack Overflow

PostgreSQL: Conditional unique constraint

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related