CODEWITHSUNDEEP
sqlsqlitecheck-constraints
Jinghui Niu
Jinghui Niu

Reputation: 1140

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?

Upvotes: 4

Views: 136

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

"Although this is solved by putting application-level logic, I still would like to see if there is any creative solution in pure database, not involving apps and triggers."

Yes,it is possible to achieve required constraint with partial unique index:

CREATE UNIQUE INDEX idx ON child (flag, parent_id) WHERE flag = 1;

db<>fiddle demo

INSERT INTO parent(id, payload) VALUES(1, 'Parent1');
INSERT INTO child(id, flag, parent_id) VALUES (1, 1, 1);
INSERT INTO child(id, flag, parent_id) VALUES (2, 0, 1);
INSERT INTO child(id, flag, parent_id) VALUES (3, 0, 1);
SELECT * FROM child;

-- trying to insert second active child will cause unique index violation
INSERT INTO child(id, flag, parent_id) VALUES (4, 1, 1)
-- UNIQUE constraint failed: child.flag, child.parent_id

Upvotes: 5

Jinghui Niu
Jinghui Niu

Reputation: 1140

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.

Upvotes: 0

Related Questions