Constraint to force pair of IDs


  • QA Engineer

    I would like a constraint to force a pair of IDs.

    For example, I have a table with two columns.

    1. car_id
    2. tire_id

    However, a specific car_id must 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 car_id of 1 is already paired with a tire_id.

    I also added the other_id just to show that this table contains other IDs that are not relevant to the constraint I want.

    Also, both car_id and tire_id have 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.

    EDIT

    The more I think about this problem the more I realize that the tire table should instead be related to the car table and this table should only be related to the car table, 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 (1,2,y) will not.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2