SQLite 3.27

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

id	b_id
1	1
2	null
3	null
4	null