I have a table x
that I've defined like this:
CREATE TABLE x (
xid INTEGER NOT NULL PRIMARY KEY,
yid INTEGER NOT NULL REFERENCES y(yid),
is_principal BOOLEAN NOT NULL
);
This definition misses one constraint that x
must satisfy. In English, this constraint could be described like this:
there may be one or more rows with a given value in the
yid
field, but among them there must always be exactly one whoseis_principal
field isTRUE
1.
I'm looking for a way to enforce this constraint.
(In case it matters, I'm particularly interested in solutions applicable to SQLite3 and PostgreSQL.)
EDIT: Just to be clear, the description above does not preclude the existence of rows in table y
whose value of yid
is not mentioned at all in table x
. For such values of yid
there is no value of xid
at all, principal or otherwise. It is only for those values of yid
that appear in table x
that there must be one and only one row in table x
having is_principal = TRUE
.
1 Another way to express the same constraint would be to say that the following two queries should always produce identical outputs:
SELECT DISTINCT yid FROM x ORDER BY yid;
SELECT yid FROM x WHERE is_principal ORDER BY yid;
Best Answer
The problem is similar to this one: How to have a one-to-many relationship with a privileged child?
The "at most one per group" part of the constraint can be solved with a partial index:
Another way to solve the problem is by removing the
is_principal
column and add a 3rd table. This doesn't solve the "exactly one" either:If you want to enforce the "exactly one" restriction using DDL alone, it can be done in Postgres with deferrable constraints (I don't think this is an option in SQLite).
For more details and options, you can see the excellent answer in the SO question by @Erwin: Complex foreign key constraint in SQLAlchemy.
(Editing the answer for the additional detail that not all values of
y.yid
have to appear in tablex
. One table is added):