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.

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.

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.

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 😁

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.

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.

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'

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)

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

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

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.