3

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 whose is_principal field is TRUE1.

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;
2

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:

CREATE UNIQUE INDEX is_FavoriteChild
  ON x (yid)
  WHERE is_principal  ;

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:

CREATE TABLE x (
    xid INTEGER NOT NULL PRIMARY KEY,
    yid INTEGER NOT NULL REFERENCES y (yid),
    UNIQUE (yid, xid)
);

CREATE TABLE x_principal (
    xid INTEGER NOT NULL,
    yid INTEGER NOT NULL PRIMARY KEY,
    FOREIGN KEY (yid, xid) REFERENCES x (yid, xid) 
);

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 table x. One table is added):

--- This table will hold all values of yid that appear in table x
CREATE TABLE y_x (
    yid INTEGER NOT NULL PRIMARY KEY REFERENCES y (yid),
    --- **no other columns**
    principal_xid INTEGER NOT NULL
);

CREATE TABLE x (
    xid INTEGER NOT NULL PRIMARY KEY,
    yid INTEGER NOT NULL REFERENCES y_x (yid)
                         DEFERRABLE INITIALLY DEFERRED,
    UNIQUE (yid, xid)
);

ALTER TABLE y_x
ADD CONSTRAINT y_principal_x_fk
   FOREIGN KEY (yid, principal_xid)
   REFERENCES x (yid, xid)
   DEFERRABLE INITIALLY DEFERRED; 
  • 1
    Thanks for this suggestion. Maybe I'm missing something, but I need to enforce "exactly one per group", not "at most one per group"... – kjo Jan 28 '15 at 19:16
  • 1
    I've edited the answer. – ypercubeᵀᴹ Jan 28 '15 at 19:26
  • Thanks again, and sorry for being such a pest. After studying your answer I realized that my post was not sufficiently clear on one point. I have edited my post to remedy this. In particular, I think that the NOT NULL clause in the principal_xid INTEGER NOT NULL spec does not hold in this case. I apologize for the confusion. – kjo Jan 28 '15 at 20:36
  • @kjo No worries, I was afraid that that might be the case. Editing the answer to fix. – ypercubeᵀᴹ Jan 28 '15 at 20:39
  • @ypercube: Your design is good. In the second scenario, inserting in x and y_x can be a bit challenging with the circular references. Data-modifying CTEs may be the best solution. – Erwin Brandstetter Jan 28 '15 at 22:55

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.