On 11/10/2018 08:18 AM, Keith Medcalf wrote:
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.
I think this is a good answer. Now implemented here:
https://sqlite.org/src/info/ae9638e9c0ad0c36
Dan.
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
