SQLite Forum

Timeline
Login

3 forum posts by user Rhys.RC

2021-09-12
22:39 Reply: Method to temporarily disable Not Null constraint? (artifact: b82245245e user: Rhys.RC)

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 😁

22:33 Reply: Method to temporarily disable Not Null constraint? (artifact: e113416265 user: Rhys.RC)

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.

2021-09-10
04:45 Post: Method to temporarily disable Not Null constraint? (artifact: 0d79a2ed87 user: Rhys.RC)

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.