CODEWITHSUNDEEP
mysqlsqlpostgresqldatabase-designforeign-keys
BroiSatse
BroiSatse

Reputation: 44675

Limit number of associated records on DB level

Let's assume I have two tables parents and children. Naturally parents may have many children with one-to-many association. Is there any construct within MySQL or PostgreSQL which would allow to limit the number of associated objects, something like:

FOREIGN KEY (parent_id) 
  REFERENCES parent(id)
  LIMIT 3

Does anything like this exists or do we need to have a custom trigger?

Upvotes: 4

Views: 378

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

Not in the definition of foreign keys. I would solve that by adding a sequential number for each child per parent like this (code for PostgreSQL, the principal is standard SQL):

CREATE TABLE child (
   child_id  serial PRIMARY KEY
 , parent_id int NOT NULL REFERENCES parent
 , child_nr  int NOT NULL
 , CHECK (child_nr BETWEEN 1 AND 3)
 , UNIQUE (parent_id, child_nr)
);

This way you can have children 1 through 3 for each parent or some or none of those. But no others.

Since you have a natural PK with (parent_id, child_nr) now, you could drop the surrogate PK column child_id. But I like to have a single-column surrogate PK for almost every table ...

You could use a trigger to limit the number, which checks how many children are there already before inserting a new one. But you'd run into concurrency issues, and it's more expensive, less reliable, easier to circumvent and vendor-specific.

How to manage child_nr?

The RDBMS just enforces (reliably) that no illegal state can ever exist in the table. How you figure out the next child_nr is up to you. Many different approaches are possible.

For just three children, you could insert all children automatically when creating a parent (with a trigger, a rule or in your application). With given (parent_id, child_nr) and additional columns NULL.

Then you would only allow UPDATE and not INSERT or DELETE for the child table (GRANT / REVOKE), or even make sure with another trigger, so that superusers cannot circumvent it. Make the FK to parent with ON DELETE CASCADE, so children die with the parent automatically.

Alternative

Somewhat less reliable, but cheaper: Keep a running count of children in the parent table and restrict it to be <= 3. Update it with every change in the child table with triggers. Be sure to cover all possible ways to alter data in the child table.

Upvotes: 2

Related Questions