Search code examples
sqlsqlitecheck-constraints

SQLite long-arm check constraint?

I have two tables in SQLite, modeling a one-to-many relationship:

CREATE TABLE parent (
    id INTEGER PRIMARY KEY,
    payload TEXT
);

CREATE TABLE child (
    id INTEGER PRIMARY KEY,
    flag BOOLEAN,
    parent_id INTEGER,
    FOREIGN KEY(parent_id) REFERENCES parent (id) ON DELETE CASCADE ON UPDATE CASCADE, 
);

Is there a way to put a CHECK CONSTRAINT on child.flag, so that there is always one and only one True among all child for any parent?

Solution

  • My research shows that there is no way to let local check constraint know its sibling status. I recommend putting the checking logic in application layer.