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.
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.
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 🙂