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

The case is about a customer who can reserve movies. But they are allowed only to hold up to 10 films in their possession at any given time. What check barrier do I need to go to work this

My database has to follow the code:?

CREATE TABLE style (PK_GenreID AutoIncrement (13, 1) not NULL, GenreNaam text (20),

primary key (PK_GenreID));

Create unique index style on index_BC00E533_C727_4D6E (GenreNaam);

CREATE TABLE Klant (PK_Klantnummer autoIncrement (19, 1) not NULL, name text (20) is not NULL, Woonplaats text (20), postcode text (6), Huisnummer text (6), Telefoon text ( 10), Email Text (255),

Contract Primary Key Primary Key (PK_Clintanamer));

Create unique index index_6827b6cc_a7f7b57 on clant (name);

Create a tablet reversing (PK_resurveringID atoininkrament (13, 1) Faucet, FK_clantnamer INTEGER, Reserveringsdatum DATE, Primary Key (PK_ReserveringID),

Constant KlantReserveringen Foreign Key (FK_Klantnummer) Reference Klant (PK_Klantnummer ) On the update CASCADE at DELETE CASCADE);

The primary key of the table films (Pike_filmnamaaidi) AUTOINCREMENT (51, 1) No N Yuelel, Fke_jenred Intejeyr, Filmnam text (30) with compression, release went on, Registrar text (30), contract Primary (Pike_filmnamaaidi),

contract Jenaretfilms forward key (Fke_jenred) reference Giniyr (Pike_jenred) update Kasad on Disle Cascade);

create index pk_filmnamide on films (pk_filename id);

Table of Belly Dredder (PK_FilmMidAtoInterrement (50, 1) Faucet, FK_FilmNamide Integer, Sort Text 50), Primary Key (PK_FilmID),

Contract Films Belvedrger Forward Key (FK-Filename ID) Reference movies (PK_Filename ID) on updated cascade on Disse Cascade);

Table Beskadiging Create (Pikebiscadijingaid Attoinkrament 14, 1) No, no, FK_FilmID INTEGER, Sled text (255), on dates, contract primaries primary key (Pike_ BeschadigingID),

contract Discuss cascade on updated cascade on Beddriver (PK_FilmID) in reference to Bilddrawar, Foreign Key (FK_FilmId);

Create Index PKBS Chidging ID On Basking (PKBSECE) hasigingID;);

TABLE Reserveringsregel (FK_ReserveringID integer, FK_FilmID integer, Begindatum date, Einddatum, DATE, the barrier BeelddragerReserveringsregel foreign key (FK_FilmID) making Beelddrager (PK_FilmID) updated on CASCADE DELETE reference CASCADE, Handicap ReserveringenReserveringsregel Foreign Key (FK_ReserveringID) CASCADE current delete Cascade updated on reference Reserveringen (PK_ReserveringID);

itemprop = "text">

This rule seems like a poor candidate for the database layer

Reservable Entity functionality (unique keys and foreign keys for them) alone will not solve it.

can be used to trigger a potential solution to short-circuit the exception) substantially in the region at any time also try to insert, update or violate want Kardineliti to be removed, however, mysql functionality Apart from the lack (depending on the version), the triggers are usually a bad idea and especially applicable to unstable business rules like you would like to do in such a proposal.

A possible solution is to make your data structure de-normalized. But with such a vastness of horror, I will not go into nuances.

A possible solution is to use meta data to maintain meta data about the prominence of your set and then implement the change. It becomes difficult and finally fails because your app code is correct to keep your meta data up to date. This pattern is often implemented and as often fails.

Finally, the best solution is to have fair use of the transaction in your app code. The general flow is like this:

1. Find out what you insert / update / delete in your app code
2. Start a transaction
4. Get locked on the set of data you made
5. Compare the change vs. the database status that you want to do
6. if it's your Passes the rules, execute SQL
7. Prepare the transaction