Suppose that I have a mini-world consisting of three types of entities, A, B, and C, subject to the following constraints:

  1. each A is associated with 0 or more B's, and each B must be associated with exactly one A;
  2. 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 A table.

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.


It seems to me that the second half of rule #2 is readily enforced with a foreign-key constraint, as you describe for rule #1.

But the first half of rule #2 seems hard to enforce, in part due to a chicken-vs-egg conflict: Are you going to make 'em add a C before there's an A, so that when you add the A, it will have its (one) C?

But you can't: that new C needs a valid pointer to an A, which doesn't exist yet, and so can't be added.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.