On Friday, 9 November, 2018 16:20, J. King wrote: >To: SQLite mailing list
>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. Foreign Key constraints should be updated notwithstanding the status of PRAGMA foreign_keys at the time the ALTER command is given. The current behaviour is inconsistent and incorrect. If one wants the "legacy behaviour" for the ALTER commands because one wants those commands to operate in the legacy fashion, then one should be using the pragma that has been provided for that purpose. This would revert to the "legacy" way of doing things. Which means doing whatever was done in legacy processing of the ALTER commands including the handling of foreign key constraints. https://sqlite.org/pragma.html#pragma_legacy_alter_table When processing an "ALTER" statement under the "new" (vs the legacy) rules, leaving a dependancy on some other setting is surprising. Unless one specifically requests "legacy" processing, the processing of an ALTER statement should never result in a database which is self-inconsistent. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. > >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 > >-- >J. King >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
