SQLite Forum

Method to temporarily disable Not Null constraint?
Login

Method to temporarily disable Not Null constraint?

(1) By Rhys (Rhys.RC) on 2021-09-10 04:45:10 [link] [source]

I need to disable or circumvent NOT NULL constraints when initially inserting data, and sadly modifying the schema or providing the values is not an option.

SQLite has PRAGMA foreign_keys = 0; & PRAGMA ignore_check_constraints = 1; but neither (understandably) disable the NOT NULL constraints.

I'm replicating the schema and data from a gargantuan SQL Server database into a SQLite database. The SQLite schema is created using the same migrations as the source database, then NHibernate (ORM) is used to copy all entities in two passes; first all root entities (non joining many to many tables) and their non-relational properties (strings, int, binary columns etc), then in the second pass the relationships between the entities are stitched back together.

Its in the fist pass that an exception is thrown:

System.Data.SQLite.SQLiteException: 'constraint failed NOT NULL constraint failed: Trainer.Home'

This happens as expected when attempting to insert entities with NOT NULL foreign key constraints that I'd like to avoid.

Why can't I just modify the schema? I can, but given that hundreds of tables are involved, finding an alternative approach would be preferable.

Why can't I just insert the data in order? Due to circular references between entities it would be preferable to follow a more simplistic procedure rather than a graph-traversal-n-pass one.

I've asked this question offsite but I probably should have asked here first.

(2) By Simon Slavin (slavin) on 2021-09-10 06:10:36 in reply to 1 [source]

Option A: remove the constraints

Option B: make a table which is a copy of the entity table, but lacks the constraints. Load your data into that. Once the data is all there (which I think means there will be no NULLs left) copy that table to the real table using the INSERT … SELECT command which is optimized for speed. Then DELETE FROM your temporary table.

Comment on your post:

given that hundreds of tables are involved

This is always a red flag in a SQL database. If two tables have the same definition, or your table names include numbers or dates, it's probably a sign that they should be the same table, with one extra column. If you're right now at a stage where you are designing the schema, think hard about this because it will save you a lot of trouble in the long run.

(6) By Rhys (Rhys.RC) on 2021-09-12 22:33:35 in reply to 2 [link] [source]

Option A: remove the constraints

Option B: make a table which is a copy of the entity table, but lacks the constraints. Load your data into that. Once the data is all there (which I think means there will be no NULLs left) copy that table to the real table using the INSERT … SELECT command which is optimized for speed. Then DELETE FROM your temporary table.

If you're aware of a programmatic method to do this, i.e. magic CLONE TABLE A TO B WITHOUT NOT NULL, rather than manually modifying 500+ migration scripts that generate SQL from C# I think I'll just have to pull my finger out and calculate the correct insertion order.

If two tables have the same definition, or your table names include numbers or dates, it's probably a sign that they should be the same table, with one extra column.

If only this were so 😢, It's a large (330 tables) relativity well designed schema that happens to map to a complex domain.

(3) By anonymous on 2021-09-10 06:17:31 in reply to 1 [link] [source]

hundreds of tables are involved

Any text editor worthy of the name should have no problems removing NOT NULL constraints from an entire schema in one pass.

(4) By Gunter Hick (gunter_hick) on 2021-09-10 06:40:08 in reply to 1 [link] [source]

Since you have foreign keys disabled, would it not be possible to insert a non-NULL token value for the relational properties? After all, the second pass is going to fix them anyway. And you can check against a suitably selected token value before enabling constraint checking.

(7) By Rhys (Rhys.RC) on 2021-09-12 22:39:10 in reply to 4 [link] [source]

This could be real winner! I'd have to avoid using the ORM but given It's mostly working against me in this context that might be sensible.

Thank you so much 😁

(5) By Rowan Worth (sqweek) on 2021-09-10 06:49:04 in reply to 1 [link] [source]

Can you just disable/defer foreign key constraints and insert everything in a single pass?

It seems to me that if you just blat the contents of each table straight from source to destination you'll be good to go. The only reason I can think that this won't work is if you're changing entity IDs during the data migration.