How to add a constraint to a table in mysql, so a client is only allowed to have 10 products in its possession?

Question

The case is about a client who can reserve films. But he is only allowed to have a max of 10 films in his possession at any given time. What check constraint do I need to let this work?

The code to create my database is as followed:

CREATE TABLE Genre (PK_GenreID AUTOINCREMENT(13, 1) NOT NULL, GenreNaam TEXT(20),

PRIMARY KEY (PK_GenreID));

CREATE UNIQUE INDEX Index_BC00E533_C727_4D6E ON Genre(GenreNaam);

CREATE TABLE Klant (PK_Klantnummer AUTOINCREMENT(19, 1) NOT NULL, Naam TEXT(20) NOT NULL, Woonplaats TEXT(20), Postcode TEXT(6), Huisnummer TEXT(6), Telefoon TEXT(10), Email TEXT(255),

CONSTRAINT PrimaryKey PRIMARY KEY (PK_Klantnummer));

CREATE UNIQUE INDEX Index_6827B6DC_E76F_4B57 ON Klant(Naam);

CREATE TABLE Reserveringen (PK_ReserveringID AUTOINCREMENT(13, 1) NOT NULL, FK_Klantnummer INTEGER, Reserveringsdatum DATE, PRIMARY KEY (PK_ReserveringID),

CONSTRAINT KlantReserveringen FOREIGN KEY (FK_Klantnummer) REFERENCES Klant(PK_Klantnummer) ON UPDATE CASCADE ON DELETE CASCADE);

CREATE TABLE Films (PK_FilmnaamID AUTOINCREMENT(51, 1) NOT NULL, FK_GenreID INTEGER, Filmnaam TEXT(30) WITH COMPRESSION, Releasedatum DATE, Regisseur TEXT(30), CONSTRAINT PrimaryKey PRIMARY KEY (PK_FilmnaamID),

CONSTRAINT GenreFilms FOREIGN KEY (FK_GenreID) REFERENCES Genre(PK_GenreID) ON UPDATE CASCADE ON DELETE CASCADE);

CREATE INDEX PK_FilmnaamID ON Films(PK_FilmnaamID);

CREATE TABLE Beelddrager (PK_FilmID AUTOINCREMENT(50, 1) NOT NULL, FK_FilmnaamID INTEGER, Soort TEXT(50), PRIMARY KEY (PK_FilmID),

CONSTRAINT FilmsBeelddrager FOREIGN KEY (FK_FilmnaamID) REFERENCES Films(PK_FilmnaamID) ON UPDATE CASCADE ON DELETE CASCADE);

CREATE TABLE Beschadiging (PK_BeschadigingID AUTOINCREMENT(14, 1) NOT NULL, FK_FilmID INTEGER, Schade TEXT(255), Datum DATE, CONSTRAINT PrimaryKey PRIMARY KEY (PK_BeschadigingID),

CONSTRAINT BeelddragerBeschadiging FOREIGN KEY (FK_FilmID) REFERENCES Beelddrager(PK_FilmID) ON UPDATE CASCADE ON DELETE CASCADE);

CREATE INDEX PK_BeschadigingID ON Beschadiging(PK_BeschadigingID);

CREATE TABLE Reserveringsregel (FK_ReserveringID INTEGER, FK_FilmID INTEGER, Begindatum DATE, Einddatum DATE, CONSTRAINT BeelddragerReserveringsregel FOREIGN KEY (FK_FilmID) REFERENCES Beelddrager(PK_FilmID) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT ReserveringenReserveringsregel FOREIGN KEY (FK_ReserveringID) REFERENCES Reserveringen(PK_ReserveringID) ON UPDATE CASCADE ON DELETE CASCADE);

Answer 1

This rule sounds like a poor candidate for the database layer to enforce.

Referential integrity functionality (unique keys and foreign keys to them) won't solve this alone.

A possible solution could be the use of a trigger to short-circuit (throw an exception) when attempting any inserts, updates or deletions that violate the desired cardinality. However mysql is quite deficient in this area of functionality (depending on the version). Also, triggers are generally a bad idea and especially so in enforcing fluctuating business rules like you propose.

A possible solution is to de-normalize your data structure. But this is such a colossally horrible idea I won't go into specifics.

A possible solution is to maintain meta data about the cardinality of your set and then make use of the meta data in enforcing change. This gets tricky and ultimately fails because your application code must be perfect in keeping this meta data up to date. This pattern is frequently implemented and just as frequently fails.

Ultimately, the best solution is the proper use of transactions in your application code. The general flow is something like this:

1. Know what insert/update/delete you want to make in your application code
2. Begin a transaction
3. Obtain locks on your set of data that has business rule limitations in cardinality
4. Compare the database state versus the change you want to make
5. If it passes your rules, execute SQL
6. Commit the transaction