Hallo, I've casually discovered that the behavior of ALTER TABLE RENAME TO (versione 3.25.x) seems to be affected by an odd glitch; FOREIGN KEY constraints are updated as expected only when PRAGMA foreign_keys=1, otherwise they are just ignored.
example (common part) --------------------- CREATE TABLE mother ( id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE daughter ( id INTEGER PRIMARY KEY, id_mother INTEGER, name TEXT, CONSTRAINT fk_one FOREIGN KEY (id_mother) REFERENCES mother (id)); test #1 ---------------------- PRAGMA foreign_keys=0; ALTER TABLE mother RENAME TO mom; SELECT sql FROM sqlite_master WHERE name = 'daughter'; ============ CREATE TABLE daughter ( id INTEGER PRIMARY KEY, id_mother INTEGER, name TEXT, CONSTRAINT fk_one FOREIGN KEY (id_mother) REFERENCES mother (id)) as you can see, the FK constraint definition has not been updated. test #2 ---------------------- PRAGMA foreign_keys=1; ALTER TABLE mother RENAME TO mom; SELECT sql FROM sqlite_master WHERE name = 'daughter'; ------------------------------------------------------ CREATE TABLE daughter ( id INTEGER PRIMARY KEY, id_mother INTEGER, name TEXT, CONSTRAINT fk_one FOREIGN KEY (id_mother) REFERENCES "mom" (id)) this second time the FK constraint has been properly updated. Note: ALTER TABLE RENAME COLUMN seems to be immune from the issue. test #3 ---------------------- PRAGMA foreign_keys=0; ALTER TABLE mother RENAME COLUMN id TO pkuid; SELECT sql FROM sqlite_master WHERE name = 'daughter'; ============ CREATE TABLE daughter ( id INTEGER PRIMARY KEY, id_mother INTEGER, name TEXT, CONSTRAINT fk_one FOREIGN KEY (id_mother) REFERENCES mother (pkuid)) test #3 ---------------------- PRAGMA foreign_keys=1; ALTER TABLE mother RENAME COLUMN id TO pkuid; SELECT sql FROM sqlite_master WHERE name = 'daughter'; ============ CREATE TABLE daughter ( id INTEGER PRIMARY KEY, id_mother INTEGER, name TEXT, CONSTRAINT fk_one FOREIGN KEY (id_mother) REFERENCES mother (pkuid)) the FK constraint definition is correctly updated in both cases, the actual setting of PRAGMA foreign_key is not relevant. best regards, Sandro Furieri (developer of SpatiaLite) _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users