Suppose that I have a mini-world consisting of three types of entities, A, B, and C, subject to the following constraints:
- each A is associated with 0 or more B's, and each B must be associated with exactly one A;
- each A is associated with 1 or more C, and each C must be associated with exactly one A.
It is easy to come up with a schema that will automatically enforce constraint 1, namely, include in the definition for the
B table a foreign key referring to the
The same idea does not quite work for constraint 2, because it does not ensure that at least 1 C is associated with each A.
Is there a standard/canonical way to enforce constraint 2? Or does it depend on the particular DB vendor? If the latter is the case, what would it be for Oracle?
EDIT: Sorry, my original wording of the constraints was incomplete. I fixed this by specifying how many A's must be associated with each B and each C, respectively.