Unique constraint over multiple tables

Question

Let's say we have these tables:

CREATE TABLE A (
    id SERIAL NOT NULL PRIMARY KEY
);
CREATE TABLE B (
    id SERIAL NOT NULL PRIMARY KEY
);
CREATE TABLE Parent (
    id SERIAL NOT NULL PRIMARY KEY,
    aId INTEGER NOT NULL REFERENCES A (id),
    bId INTEGER NOT NULL REFERENCES B (id),
    UNIQUE(aId, bId)
);
CREATE TABLE Child (
    parentId INTEGER NOT NULL REFERENCES Parent (id),
    createdOn TIMESTAMP NOT NULL
);

Is it possible to create a unique constraint on Child such that for all rows in Child at most one references a Parent having some value of aId? Stated another way can I created a unique constraint so that the join of the above tables will have no duplicate aId? I'm thinking not--the grammars of every database I could find seem tied to one table per constraint--but that might be a lack of imagination on my part. (De-normalizing to include aId on Child is one solution, of course.)

Subtractive

You can use a Trigger on Insert or Update to execute and respond to your constraint logic, but I think triggers should be an absolute last resort -- I'd rather do this in the business layer. – Normalize

I agree with hamlin11 on this accept I'd rather not see this done in the business layer, but with a database trigger in most cases. – Casualty

BUsiness layer would be the worst possible palace to put such logic, if it is too smplex for a constraint a trigger is the right way to go. It must of course be a properly written trigger that performs well(badly written triggers are what give triggers a bad rep). – Conservative

Answer 1

You could try the following. You have to create a redundant UNIQUE constraint on (id, aId) in Parent (SQL is pretty dumb isn't it?!).

CREATE TABLE Child
(parentId INTEGER NOT NULL,
 aId INTEGER NOT NULL UNIQUE,
FOREIGN KEY (parentId,aId) REFERENCES Parent (id,aId),
createdOn TIMESTAMP NOT NULL);

Possibly a much better solution would be to drop parentId from the Child table altogether, add bId instead and just reference the Parent table based on (aId, bId):

CREATE TABLE Child
(aId INTEGER NOT NULL UNIQUE,
 bId INTEGER NOT NULL,
FOREIGN KEY (aId,bId) REFERENCES Parent (aId,bId),
createdOn TIMESTAMP NOT NULL);

Is there any reason why you can't do that?

Calico

I like the second approach. No need for triggers, tables are normalized. The (aId,bId) could be used as PRIMARY KEY of Child as well. – Holle

Sorry, I meant aId could be used as PRIMARY KEY of Child. – Holle

Answer 2

The proper way to do this would be to do away with the Child table altogether and put the createdOn column in the Parent table, without the NOT NULL constraint. All you are saying is that one Parent entry can have zero or one (but not more) createdOn values. You don't need a separate table for that. The fact that it is not easy or obvious to do otherwise partially proves my point. ;-) SQL usually works out that way.

Ignaciaignacio

But that wouldn't enforce the uniqueness of aId. The key in the parent table is (aId, bId) not aId. – Calico