1

For example, let say DB has foreign key A.b_id -> B.id with SET NULL on delete. If record with some B.id get deleted, all b_id references will be set to NULL.

But if A already contains record where A.b_id has value that is not in B.id (it was inserted without foreign keys support), is there a way to force SQLite DB check foreign keys and set to NULL such data?

In fact, in first place I'm solving an DB upgrading task.

On start app checks if internal DB (resource) has higher version than user DB. If so it backups user DB, copies internal empty DB to user storage. Than turns off foreign keys support and fills new DB with data from backup, inserting automatically in loop table by table for all columns with same name. Turns on foreign keys support back.

Everything works fine, but if in some table in old DB there is no foreign key constrain previously, while new DB has one, the data will be inserted as is and link can point nowhere (possibly wrong links is unavoidable and not related to question).

Yes, I understand a way to insert without turning off foreign keys support, but it would need knowledge of tables dependencies order that I would like to avoid.

Thanks for any help in advance!

forpas
  • 160,666
  • 10
  • 38
  • 76
Oleg
  • 101
  • 7
  • Turning on foreign key support does not enforce any referential integrity checks for existing data. – forpas Apr 06 '21 at 14:25
  • Yes, I know. So, as you can see, I'm interesting is there a command to force such checks. – Oleg Apr 06 '21 at 14:26

1 Answers1

1

Although I don't know of a way that automatically will set to NULL all orphaned values of a column in a table that (should) reference another column in another table, there is a way to get a report of all these cases and then act accordingly.
This is the PRAGMA statement foreign_key_check:

PRAGMA schema.foreign_key_check;

or for a single table check:

PRAGMA schema.foreign_key_check(table-name);

From the documenation:

The foreign_key_check pragma checks the database, or the table called "table-name", for foreign key constraints that are violated. The foreign_key_check pragma returns one row output for each foreign key violation. There are four columns in each result row. The first column is the name of the table that contains the REFERENCES clause. The second column is the rowid of the row that contains the invalid REFERENCES clause, or NULL if the child table is a WITHOUT ROWID table. The third column is the name of the table that is referred to. The fourth column is the index of the specific foreign key constraint that failed. The fourth column in the output of the foreign_key_check pragma is the same integer as the first column in the output of the foreign_key_list pragma. When a "table-name" is specified, the only foreign key constraints checked are those created by REFERENCES clauses in the CREATE TABLE statement for table-name.

Check a simplified demo of the way to use this PRAGMA statement, or its function counterpart pragma_foreign_key_check().

You can get a list of the rowids of all the problematic rows of each table.

In your case, you can execute an UPDATE statement that will set to NULL all the orphaned b_ids:

UPDATE A
SET b_id = NULL
WHERE rowid IN (SELECT rowid FROM pragma_foreign_key_check() WHERE "table" = 'A')

This also works in later versions of SQLite:

UPDATE A
SET b_id = NULL
WHERE rowid IN (SELECT rowid FROM pragma_foreign_key_check('A'))

but it does not seem to work up to SQLite 3.27.0

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Nice, thanks. According to https://www.sqlite.org/releaselog/3_7_16.html foreign_key_check was added in sqlite 3.7.16. I've checked with SQLite 3.24.0 it works. In addition to answer, pragma_foreign_key_list(table) can be used in general to get orphaned field names (b_id in our example) from the index of the specific foreign key constraint that failed (the fourth column of pragma_foreign_key_check return) – Oleg Apr 07 '21 at 07:56
  • @Oleg yes, though in 3.27.0 the function pragma_foreign_key_check() with the table's name as a parameter does not work, but it works without it and a WHERE clause. – forpas Apr 07 '21 at 08:00