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 [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 [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.

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.

(8) By tom (younique) on 2022-05-16 22:52:55 in reply to 1 [link] [source]

Sorry for bringing this old topic up again, but I stumbled into the same problem and think that an option to temporarily disabling null-constraint enforcement would indeed be helpful.

My situation:

ALTER TABLE x ADD COLUMN y NOT NULL

This will fail because SQL(ite) won't know what values to insert.

I could, of course, use ALTER TABLE x ADD COLUMN y NOT NULL DEFAULT 0, but then I can't remove the "DEFAULT 0" declaration afterwards.

(9) By ddevienne on 2022-05-17 06:56:40 in reply to 8 [link] [source]

I don't know of a way to bypass NOT NULL enforcement.

But there is a work-around, if you replace NOT NULL with your own CHECK constraint.

Kinda hackish, and ugly, but I'm throwing it out there just in case.

With NOT NULL:

C:\Users\ddevienne>sqlite3
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(v not null);
sqlite> insert into t values (null);
Error: NOT NULL constraint failed: t.v
sqlite> pragma ignore_check_constraints;
0
sqlite> pragma ignore_check_constraints = 1;
sqlite> pragma ignore_check_constraints;
1
sqlite> insert into t values (null);
Error: NOT NULL constraint failed: t.v

With custom CHECK constraint equivalent to NOT NULL:

sqlite> drop table t;
sqlite> create table t(v check (typeof(v) <> 'null'));
sqlite> insert into t values (null);
sqlite> pragma ignore_check_constraints = 0;
sqlite> insert into t values (null);
Error: CHECK constraint failed: typeof(v) <> 'null'

(10) By Donald Griggs (dfgriggs) on 2022-05-17 14:42:13 in reply to 9 [link] [source]

DDevienne, that does seem useful.

FYI, it seems that
check (v is not null) can be used as well as
check (typeof(v) <> 'null')

-- test commands to copy/paste:

pragma ignore_check_constraints = 1;
pragma ignore_check_constraints;
drop table if exists t;
create table t(v check (v is not null));
insert into t values (null);
select rowid from t where v is null;
pragma ignore_check_constraints = 0;
insert into t values (null);

Results:

SQLite version 3.38.5 2022-05-06 15:25:27
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma ignore_check_constraints = 1;
sqlite> pragma ignore_check_constraints;
1
sqlite> drop table if exists t;
sqlite>
sqlite> create table t(v check (v is not null));
sqlite> insert into t values (null);
sqlite> select rowid from t where v is null;
1
sqlite> pragma ignore_check_constraints = 0;
sqlite> insert into t values (null);
Runtime error: CHECK constraint failed: v is not null (19)

(11) By ddevienne on 2022-05-17 14:53:15 in reply to 10 [link] [source]

check (v is not null) can be used as well as

Good point. Probably a tiny bit faster too. Thanks.

(12) By bokwoon on 2022-05-18 08:22:26 in reply to 1 [link] [source]

This seems to be an XY problem. You want to disable NOT NULL constraints but your ultimate goal is copying data from one database to another.

For some reason your ORM of choice has decided to fill in all foreign keys as NULL first -- that's the problem. The standard procedure is to disable foreign keys, copy all the data in one pass and then re-enable foreign keys. Not by filling in foreign keys as NULL in the first pass, then populating them in a second pass.