SQLite 3.8

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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 
);
CREATE UNIQUE INDEX idx ON child (flag, parent_id) WHERE flag = 1;
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


id
flag
parent_id


1
1
1

2
0
1

3
0
1


INSERT INTO child(id, flag, parent_id) VALUES (4, 1, 1)
-- UNIQUE constraint failed: child.flag, child.parent_id


id
flag
parent_id


1
1
1

2
0
1

3
0
1


UNIQUE constraint failed: child.flag, child.parent_id