23

I'm using SQLite, which doesn't support adding a constraint to an existing table.

So I can't do something like this (just as an example):

ALTER TABLE [Customer]
ADD CONSTRAINT specify_either_phone_or_email
CHECK (([Phone] IS NOT NULL) OR ([Email] IS NOT NULL));

Are there any workarounds for this scenario?

I know:

  • I can add a constraint for a new table, but it isn't new (and it's generated by my ORM, EF Core)
  • I can do a "table rebuild" (rename table, create new one, copy old data, drop temp table) but that seems really complex

Ideas

  • Can I somehow make a copy of the table into a new table, with some schema changes?
  • Or "get" the schema somehow, and edit it in a SQL script, then add a table with that schema?
CL.
  • 173,858
  • 17
  • 217
  • 259
grokky
  • 8,537
  • 20
  • 62
  • 96
  • Another idea (_which I have not yet tried_) to circumvent this (_which I have just encountered and think is a mildly horrible restriction_) is to add a column with the constraint, duplicate the data into it (¿what is that in SQLish?), drop the old column and rename the new column. ¿But how would that interact with other things that refer to the existing column? & ¿What are the conditions for it to be feasible? (If the table has no primary key I think it would be tough.) – PJTraill Jun 16 '22 at 14:58

1 Answers1

21

To make a copy of a table with some schema changes, you have to do the creation and the copying manually:

BEGIN;
CREATE TABLE Customer_new (
    [...],
    CHECK ([...])
);
INSERT INTO Customer_new SELECT * FROM Customer;
DROP TABLE Customer;
ALTER TABLE Customer_new RENAME TO Customer;
COMMIT;

To read the schema, execute .schema Customer in the sqlite3 command-line shell. This gives you the CREATE TABLE statement, which you can edit and execute.

To change the table in place, you can use a backdoor.

First, read the actual table definition (this is the same as what you would get from .schema):

SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'Customer';

Add your CHECK constraint to that string, then enable write access to sqlite_master with PRAGMA writable_schema=1; and write your new table definition into it:

UPDATE sqlite_master SET sql='...' WHERE type='table' AND name='Customer';

Then reopen the database.

WARNING: This works only for changes that do not change the on-disk format of the table. If you do make any change that changes the record format (such as adding/removing fields, or modifying the rowid, or adding a constraint that needs an internal index), your database will blow up horribly.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Using the CLI isn't practical in my case as I'm trying to do it programmaticaly, and the schema is generated by EF Core. However, the "backdoor" option is interesting. Assuming that I run it straight after the table is created by the ORM, and all I do is add the check constraint like you explained, it should be safe? – grokky Mar 23 '17 at 10:05
  • You can run it at any time. But if the table is empty, simply dropping and re-creating it (like in the first option) is less dangerous. – CL. Mar 23 '17 at 10:10
  • Dropping and recreating it means I must hand-code the sql, whereas using the backdoor means I can rely on the ORM to get it right, and compile-time safety. It's an appealing option under those conditions. Thanks! – grokky Mar 23 '17 at 10:19
  • Should I do a `PRAGMA writable_schema=0;` when I'm done? The docs weren't clear if it sticks after the transaction or not. – grokky Mar 23 '17 at 10:21
  • You can re-execute what you got from `SELECT sql FROM sqlite_master`. – CL. Mar 23 '17 at 12:03
  • `PRAGMA writable_schema=0;` is useless; you must reopen the database to refresh any caches. – CL. Mar 23 '17 at 12:03
  • I've gotten sqlite_master changes to stick, on both OSX and Win, without reopening by executing ```VACUUM``` after executing ```PRAGMA writable_schema=0```-- at least for changing column constraints such as NULL/NOT NULL. Not sure if this approach still works for other kinds of changes. – mwag Sep 04 '17 at 16:56
  • @mwag VACUUM rewrites the entire database; the CREATE/DROP TABLE method would be faster. – CL. Sep 04 '17 at 20:53
  • @CL, fair point, but when I need to make schema changes-- typically for updates to software that use an sqlite3 db as a controller table-- I still prefer modifying sqlite_master and using VACUUM, because a) speed doesn't matter b/c the rest of the update takes way longer, and b) more importantly, no additional statements are necessary to recreate indexes. For me, in these cases, shorter and simpler code with unnoticeably-longer performance wins hands down. – mwag Sep 04 '17 at 22:40