change column check constraint
(1) By anonymous on 2022-11-24 07:45:12 [link] [source]
Hello
Is it possible to change the check constraint of a single column? I will put additional values in the allowed list.
I have read the "alter table" documentation but could not find it.
Thank you
(2) By ddevienne on 2022-11-24 09:32:53 in reply to 1 [source]
There's no native support for it, no.
The only option remaining, which is a bit dangerous, is to use the writable_schema pragma.
You allow it, edit sqlite_schema, i.e. SQL UPDATE sqlite_schema.sql
for the table you want to modify,
by using textual substitutions in the DDL, then reset writable_schema, which forces SQLite to reload the schema.
And if you mess up the edit, your DB is toast :).
Of course, the edits you make must be innocuous to already inserted data, their indexes and constraints.
Sounds like yours is in that category, so it would work fine.
I've successfully upgraded DBs using this technique, messing up many times until I got the edits robust enough and just right.
The safe alternative is of course to create a new table (or DB) with the new DDL, and transfer the data over.
The above is only if you want to do the upgrade in-place, typically for (very) large tables.
So start with the safe approach, see if it's fast enough for you, and only if it isn't, reach for the foot-gun!
(3) By anonymous on 2022-11-24 09:53:09 in reply to 2 [link] [source]
Thank you for the foot-gun. My table is quit large.
(4) By ddevienne on 2022-11-25 08:57:32 in reply to 3 [link] [source]
Of course, you should probably check the DB after your changes. Just in case.
PRAGMA integrity_check -- or quick_check
PRAGMA foreign_key_check -- if any FKs
(5) By anonymous on 2022-11-25 10:04:23 in reply to 3 [link] [source]
If you go down the path of pragma writable_schema then I would recommend the following:
1 - test thoroughly on a copy of the schema 2 - script everything, the process should be automated from start to finish so you won't forget or make a mistake in any step 4 - keep a copy of the original sql you are modifying, in many cases that's a good enough rollback plan