As a personal project I am working on a database to keep track of cars as they are manufactured and then sent to dealerships. One of my constraints is that certain dealerships can only have a certain number of cars of the same make. For example, DealerA can have 12 Hondas, 10 Audis, and 8 VWs. I have a table which keeps track of all the manufactured cars and then is updated with the dealership when it is shipped. I want to add a constraint in the database which stops you from changing a car's dealership field to a given dealer if the number of cars of that type is already at the dealership's limit.

In other words, if the count of cars of typeA assigned to dealerA (in the Car table) is less than typeA constraint for dealerA (in Dealership table) then allow another car of typeA to be assigned to dealer A, otherwise do not allow the change.

I am quite new to SQL and I am using this project as a way to teach myself.

  • 1
    number based limits on the number of relationships aren't an easy thing to do in SQL.
    – danblack
    Feb 2 '20 at 23:55
  • A trigger would do it, at the cost of reduced performance.
    – mustaccio
    Feb 3 '20 at 2:57
  • CHECK constraint in MySQL does not allow to use subselects or to use table records other then checked one. You may use trigger or insert via stored procedure (not a query) with proper checking logic.
    – Akina
    Feb 3 '20 at 4:37

What you're looking for is called an assertion. While these are in the ANSI standard few DBMS implement them.

The closest alternative is to write code in an AFTER trigger that throws an exception if the desired condition isn't met.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.