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
