You have two issues here:
- Presence: there cannot be a parent row without at least one child row.
- Exclusivity: there cannot be a parent row with more than one child row.
On a DBMS that supports deferred constraints (including PostgreSQL and Oracle), both of these goals can be achieved declaratively:
There is a circular foreign key between gastropod.snail_id and snail.snail_id, and also between gastropod.slug_id and slug.slug_id. There is also a CHECK that ensures exactly one of them matches gastropod.gastropod_id (and the other is NULL).
To break the chicken-and-egg problem when inserting new data, defer one direction of foreign keys.
Here is how this would be implemented in PostgreSQL:
CREATE TABLE gastropod (
gastropod_id int PRIMARY KEY,
snail_id int UNIQUE,
slug_id int UNIQUE,
CHECK (
(slug_id IS NULL AND snail_id IS NOT NULL AND snail_id = gastropod_id)
OR (snail_id IS NULL AND slug_id IS NOT NULL AND slug_id = gastropod_id)
)
);
CREATE TABLE snail (
snail_id int PRIMARY KEY,
FOREIGN KEY (snail_id) REFERENCES gastropod (snail_id) ON DELETE CASCADE
);
CREATE TABLE slug (
slug_id int PRIMARY KEY,
FOREIGN KEY (slug_id) REFERENCES gastropod (slug_id) ON DELETE CASCADE
);
ALTER TABLE gastropod ADD FOREIGN KEY (snail_id) REFERENCES snail (snail_id)
DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE gastropod ADD FOREIGN KEY (slug_id) REFERENCES slug (slug_id)
DEFERRABLE INITIALLY DEFERRED;
New data is inserted like this:
START TRANSACTION;
INSERT INTO gastropod (gastropod_id, snail_id) VALUES (1, 1);
INSERT INTO snail (snail_id) VALUES (1);
COMMIT;
However, attempting to insert only parent but not child fails:
START TRANSACTION;
INSERT INTO gastropod (gastropod_id, snail_id) VALUES (2, 2);
COMMIT; -- FK violation.
Inserting the wrong kind of child fails:
START TRANSACTION;
INSERT INTO gastropod (gastropod_id, snail_id) VALUES (2, 2);
INSERT INTO slug (slug_id) VALUES (2); -- FK violation.
COMMIT;
And inserting setting too few, too many, or mismatched fields in the parent also fails:
INSERT INTO gastropod (gastropod_id) VALUES (2); -- CHECK violation.
...
INSERT INTO gastropod (gastropod_id, snail_id, slug_id) VALUES (2, 2, 2); -- CHECK violation.
...
INSERT INTO gastropod (gastropod_id, snail_id) VALUES (1, 2); -- CHECK violation.
On a DBMS that doesn't support deferred constraints, exclusivity (but not presence) can be declaratively enforced like this:
Under a DBMS that supports calculated fields (such as Oracle 11 virtual columns), the type discriminator type doesn't need to be physically stored at the level of the child tables (only the parent table).
The unique constraint U1 may be necessary on DBMSes that don't support FK referencing super-set of key (pretty much all of them, as far as I know), so we make this super-set artificially.
Whether all this should actually be done in practice is another matter. This is one of these situations where enforcing some aspects of data integrity at the application level may be justified by the reduction of overhead and complexity.
