SQLite long-arm check constraint?

Question

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?

*The expression of a CHECK constraint may not contain a subquery.* So, no. You might be able to use a trigger to get the same effect, though. — Shawn, Jun 03 '19 at 11:13

Lukasz Szozda · Answer 1 · 2019-06-07T18:25:15.977

5

"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

edited Jun 07 '19 at 18:25

answered Jun 07 '19 at 18:19

Lukasz Szozda

162,964
23
234
275

This index seems to only solve half of the problem, namely ensuring there is `only one` entry with flag on. How to ensure there is `always one`? it doesn't seem to prevent deletion, from my reading? – Jinghui Niu Jun 07 '19 at 20:14
1

@JinghuiNiu Are you aware that such requirement if defined declaratively would require to do delete operation and update/insert at once(like MERGE) or update two records at once(setting the new one with flag =1 and immediately setting existing one to 0). More info: https://stackoverflow.com/a/10292449/5070879 – Lukasz Szozda Jun 07 '19 at 21:24
so you mean, we are stuck in this miserable SQL world?:) – Jinghui Niu Jun 08 '19 at 02:54
@JinghuiNiu I strongly oppose to **miserable SQL world**. It is powerful and descriptive language. The point is that the requirement you are proposing is not achievable in declarative manner(you could write code using stored procedure/triggers) that would guard 1:1 partial relationship. If you want it as declarative part then you need [multirows constraints aka. assertions](https://community.oracle.com/ideas/13028)(not supported by SQLite). – Lukasz Szozda Jun 08 '19 at 06:50
1

@JinghuiNiu Updates/deleting of existing values would require to handle multiple rows at once(of course it could be achieved with deferred constraints) and at COMMIT time everything will be in place. – Lukasz Szozda Jun 08 '19 at 06:52
Yes I agree, and I highly appreciate your suggestion. Just let this problem hanging for a while to open it for some other creativeness. Thank you. – Jinghui Niu Jun 08 '19 at 06:52

score 0 · Accepted Answer · answered Jun 03 '19 at 20:40

0

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.

answered Jun 03 '19 at 20:40

Jinghui Niu

990
11
28

SQLite long-arm check constraint?

2 Answers2