Foreign keys are ignored by default and need to be explicitly enabled. I would expect this to include everything that relates to foreign keys.
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von a.furi...@lqt.it Gesendet: Donnerstag, 08. November 2018 17:43 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] found a glitch in ALTER TABLE RENAME (3.25.x) 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 ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
