Database: How to distinguish "one-or-more" and "zero-or-more" relationship?

Question

Design tools allow distinguish between "one TO zero-or-more" and "one TO one-or-more" relationships. I could imagine how to implement "one TO zero-or-more" relation:

---

CHILD_TABLE

(pk) chid_id
(fk) parent_id (required)

---

PARENT_TABLE

(pk) parent_id

---

How to implement "one TO one-or-more" relationship? How to say that parent requires at least one child? Or is "one-or-more" usually implemented as "zero-or-more" ?

Answer 1

It is obvious that enforcing constraints such as the one you specify, will require the clients to sometimes issue (and the DBMS engines to accept) something that could be labeled "simultaneous updates", that is, more than one distinct table being updated prior to any constraint checking.

The SQL language (I mean, the standard) offers support for that through CREATE ASSERTION. Alas, no currently existing engine supports this statement.

The only way this can be achieved with currently existing SQL engines, is to defer the constraint checking until after all updates are done (if your engine supports this, of course). Enforcing such constraints in application code or business logic ultimately amounts to not having the constraint at all in the first place, if your database is "shared" and can also be updated by other programs.

Systems do exist that offer support for enforcing your kind of constraint, but they are not SQL sytems.

aioobe's solution is quite original, but keep in mind that you can only do so by duplicating all columns of the "many" (/child) side to the "one" (/parent) side (because otherwise you still have the same problem between two tables, except that the "or-more" part has gone away, but that's not where the problem is). And if you do this, you will then be faced with great difficulties when :

• writing queries against the "many" side (you will have to see to it that that one other row that resides on the "one" side will always get UNIONed with the many side),
• enforcing keys on the "many" side (you will have to see to it that no key on the many side will have the same value as that one other row that resides on the "one" side,
• enforcing referential integrity when the referred table is itself your "one-or-more" side (you should see to it that it is also valid for references to exist to that one other row that resides on the "one" side).

So aioobe's solution, allthough original, is likely to create more new problems than it solves.