Constraint to force pair of IDs
I would like a constraint to force a pair of IDs.
For example, I have a table with two columns.
However, a specific
car_idmust always be paired with the same
tired_id. Meaning a car can only have one specific tire.
car_id tire_id other_id 1 1 1 2 2 2 3 8 3 4 3 4 1 1 5 3 8 6 1 9 7
In the above example, all values are acceptable, except
(1, 9), because a
1is already paired with a
I also added the
other_idjust to show that this table contains other IDs that are not relevant to the constraint I want.
tire_idhave FKs to their respective tables.
Note, I am specifically using MySQL, but I'm curious if there's a specific solution for other flavors of SQL.
The more I think about this problem the more I realize that the
tiretable should instead be related to the
cartable and this table should only be related to the
cartable, but assuming I can't change the table structure, is there a different solution for what I'm looking for?
As you wrote "but I'm curious if there's a specific solution for other flavours of SQL"
This would be possible in Postgres using an https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION
alter table tires add constraint single_tire exclude using gist (car_id with =, tire_id with <>);
So the first combination of
(car_id, tire_id)will then constrain all subsequent ones. If there is a row with
(1,1,x), inserting another row with
(1,1,y)will work, but inserting