On November 9, 2018 2:50:56 AM EST, Dominique Devienne <ddevie...@gmail.com> wrote: >On Fri, Nov 9, 2018 at 8:26 AM Simon Slavin <slav...@bigfraud.org> >wrote: > >> On 9 Nov 2018, at 7:11am, Hick Gunter <h...@scigames.at> wrote: >> > Foreign keys are ignored by default and need to be explicitly >enabled. I >> would expect this to include everything that relates to foreign keys. >> > 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. >> >> While your statement is correct, I would argue that the behaviour is >> harmful, and cannot possibly be what any programmer could want to >happen. >> Think how difficult it would be to diagnose this having happened, and >then >> fix the problem it would create. >> I might argue that correct behaviour would be to transform FOREIGN >KEY >> constraints even when the PRAGMA is off. Or I might argue that the >attempt >> should generate an error result (SQLITE_MISUSE ?). >> > >Foreign keys are ignored in the sense they are not enforced, but they >are >still parsed AFAIK. >As such, I agree with Simon that it's harmful to not update them on >table >(or column) renames. --DD >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
That could lead to loss of referential integrity when modifying a table in a way not supported by ALTER TABLE, I believe. One usual method is to turn foreign keys off, rename the old table, create a new modified table under the old name, insert the old table's data into the new, and then drop the old table. If foreign keys were, for lack of a better term, partially enforced, this process would yield new and backwards-incompatible results: when the old table is renamed all referring tables would continue to refer to the old table, and when the table is dropped all references would become invalid. -- J. King _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
