SQLite Forum

Removing not null constraint doesn't behave correctly
It's documented simply because it is possible to do, but it comes with Zero guarantees of stability.

If you enter the unsafe direct editing of the master table, SQLite is relinquishing responsibility and you are taking control of it. At that moment, you and only you become solely responsible for up-keeping the validity of what is in that table.  You seem to take responsibility, then let sqlite do some changes... that is never viable (though it may succeed by chance).

With that understood, looking at your example and subsequent notation that closing connections work, I believe the thing that happens is you open 2 connections (at different points probably), changing the schema on one connection while the other doesn't know about it, then the other is trying to adjust what it believes to be the current schema, which is obviously done wrong.

I do see your adjusting the PRAGMA schema_version, but am not sure if this will be detected by the other connection, nor am I sure that it isn't already "2" (which would signify no change at all).

All of these are guesses at what is going on.

Further to that, the change you are trying to make seems a design-time change, not a production necessity - why not use a DB management tool to do that? 
If not, then you can use some scripts that do it right. 

Should be able to google it, or I can send you one (we use internally in SQLiteSpeed), but it's rather straight-forward:

- Rename the table to change (ttc) to ttc_old (or similar)
- Make the new table with the new schema (CREATE ttc(ID, c1, ...);)
- Do a simple query INSERT INTO ttc SELECT ID, c1, ... FROM ttc_old;
  (Ensure the SELECT creates columns in the same footprint as the new ttc)
- DROP TABLE ttc_old;


- No schema pragma needed 
- 100% accurate
- Multiple-connection-safe
- Change/Add multiple columns at the same time 
- Has the deficit of needing a lot of space when that table is severely massive

You may need to:

- disable/enable foreign keys before and aft.
- depending on if you use legacy mode, drop and recreate indexes and triggers on ttc too.
- adjust the data before-hand if the new schema has constraints that will fail but were allowed in the old schema.
- Do it all in a transaction which you can roll back if any part fails, obviously.

Hope that helps!