database schema - Add constraint to existing SQLite table

Question

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?

Answer 1

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.