Exclusion constraint for text values

All we need is an easy explanation of the problem, so here it is.

I have two tables. A client table and a vehicle table, the two are linked in a unit lookup table.
My constraint is that a vehicle cannot belong to a client if it already is associated with another client.

This is what I have so far:

ALTER TABLE unit 
   ADD CONSTRAINT unit_vehicle_across_client_constraint 
   EXCLUDE USING GIST (client WITH =, vehicle WITH <>) DEFERRABLE;

But it doesn’t seem to work. I guess I just don’t really understand how the exclusion parameters really work.

Any suggestions?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

As suggested a simple UNIQUE constraint on vehicle does the trick. Not sure why I wanted to do complicated.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply