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`?

Answer: 
> 
> *"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](https://www.sqlite.org/partialindex.html):

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

```

**[db<>fiddle demo](https://dbfiddle.uk/?rdbms=sqlite_3.8&fiddle=6e46710c45a5c232d1d34a43b78654a2)**

```
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

```
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.