By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE B(id INTEGER PRIMARY KEY);
✓
INSERT INTO B(id) VALUES (NULL);
✓
SELECT * FROM B
| id |
|---|
| 1 |
CREATE TABLE A(id INTEGER PRIMARY KEY, b_id INTEGER REFERENCES B(id));
✓
INSERT INTO A(b_id) VALUES (1), (2), (3), (4);
✓
SELECT * FROM A
| id | b_id |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
PRAGMA foreign_key_check;
| table | rowid | parent | fkid |
|---|---|---|---|
| A | 2 | B | 0 |
| A | 3 | B | 0 |
| A | 4 | B | 0 |
SELECT * FROM pragma_foreign_key_check();
| table | rowid | parent | fkid |
|---|---|---|---|
| A | 2 | B | 0 |
| A | 3 | B | 0 |
| A | 4 | B | 0 |
SELECT "table",
GROUP_CONCAT(rowid) orphaned_rowids
FROM pragma_foreign_key_check()
GROUP BY "table";
| table | orphaned_rowids |
|---|---|
| A | 2,3,4 |
UPDATE A
SET b_id = NULL
WHERE rowid IN (SELECT rowid FROM pragma_foreign_key_check() WHERE "table" = 'A')
✓
SELECT * FROM A
| id | b_id |
|---|---|
| 1 | 1 |
| 2 | null |
| 3 | null |
| 4 | null |