'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:

  1. Ball may or may not be in a bag
  2. Ball can be only in one bag
  3. Bag always contains at least one ball
  4. 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