'Enforcing business rule with database consistency checks
I have two kinds of entities -- ball and bag. Balls have identity (e.g number), color and other properties not important for this discussion, bag has identity and other properties. There are following rules:
- Ball may or may not be in a bag
- Ball can be only in one bag
- Bag always contains at least one ball
- If bag contains more than one ball, all these balls of same color.
I am trying to design database in such way that these rules are enforced by database, like following:
create table ball(id pkey, color text not null, bag_id nullable references bag(id));
create table bag(id pkey)
This representation guarantees rules (1) and (2), but not (3) and (4). I can enforce (4) too by adding some redundancy and composite foreign keys:
create table ball(id pkey, color text not null);
create table bag(id pkey);
create table link(bag_id references bag(id), ball_id, ball_color
references ball(id, color));
create unique index on link(ball_id);
create unique index on link(bag_id, ball_color);
Is there any way to enforce all 4 rules with native database consistency check capabilities? Is it considered good idea to try to? Is there any theory on what class of constraints can be expressed this way?
I understand that I can implement absolutely arbitrary rules using deferrent check triggers, but that is just a brute force.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
