sqlite 3.26.0 db corruption regression

up.sql

-- # steps to reproduce
-- rm -f foo.db; sqlite3 foo.db < up.sql; echo '.schema' | sqlite3 foo.db
--
-- debian stable (works as expected):
-- 3.16.2 2017-01-06 16:32:41 a65a62893ca8319e89e48b8a38cf8a59c69a8209
--
-- archlinux (corrupted database):
-- 3.26.0 2018-12-01 12:34:55 bf8c1b2b7a5960c282e543b9c293686dccff272512d08865f4600fb58238b4f9

CREATE TABLE foo (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
);

CREATE TABLE bar (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    foo_id INTEGER NOT NULL,
    FOREIGN KEY(foo_id) REFERENCES foo(id) ON DELETE CASCADE
);

-- this is the recommended way for some alter table operations
-- https://www.sqlite.org/lang_altertable.html
PRAGMA foreign_keys=off;

ALTER TABLE foo RENAME TO _foo_old;

CREATE TABLE foo (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
);

DROP TABLE _foo_old;

PRAGMA foreign_keys=on;

-- your database is now corrupted, bar has a foreign key constraint to _foo_old:
/*
CREATE TABLE bar (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    foo_id INTEGER NOT NULL,
    FOREIGN KEY(foo_id) REFERENCES "_foo_old"(id) ON DELETE CASCADE
);
*/

Hi,

This is as expected. As of 3.26.0, when a table is renamed using ALTER TABLE RENAME TO then all references to it in REFERENCES clauses that are part of other tables are also updated. You can turn this behaviour off (restoring the 3.24.0 behaviour) using "PRAGMA legacy_alter_table = ON". This probably should be documented as part of the ALTER TABLE documentation.

Technically speaking, the procedure used in the script is not actually what is recommended on sqlite.org. The recommendation would be:

 CREATE TABLE new_foo (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL);
 INSERT INTO new_foo SELECT * FROM foo;
 DROP TABLE foo;
 ALTER TABLE new_foo RENAME TO foo;

which I think would probably work.

Regards,
Dan Kennedy.