On 11/29/2018 07:10 PM, szmate1618 wrote:
I'm afraid I still didn't make any progress on this.
It looks to me that PRAGMA foreign_key_check; and the deferred foreign key
mechanism sometimes contradict each other,
consequently at least one of these features must have a bug, but I don't
know for sure which one,
so I can't risk using any of them in production until the situation is
resolved.
Do you have any suggestion how I should go about it? Can I escalate this to
someone?
If there is a bug, please do. I haven't actually understood the
explanation of it yet though.
Or what is wrong with:
PRAGMA foreign_keys = 0;
BEGIN;
<do lots of stuff>
if( [PRAGMA foreign_key_check]=="ok" ) COMMIT else ROLLBACK;
PRAGMA foreign_keys = 1;
Dan.
Thanks in advance,
Máté
szmate1618 <szmate1...@gmail.com> ezt írta (időpont: 2018. nov. 23., P,
17:21):
"PRAGMA foreign_keys = ?" is a property of the connection only, not the
database file. So what advantage would there be in including the PRAGMA
statements in the body of a transaction?
Not much, if I do everything right, I guess. But what if I break the
foreign key integrity?
If it's inside a transaction, I can rollback easily, given that I realize
I broke it.
But if I don't, because foreign key checks are turned off, and I commit
everything before noticing that
something's wrong, that's a lot harder to fix.
I can either take extra care not to break anything, or just do a backup of
the database file before
'risky' transactions, but it would be much easier if I could just use
deferred foreign keys that don't
let me commit if the database is in an inconsistent state.
Máté
Dan Kennedy <danielk1...@gmail.com> ezt írta (időpont: 2018. nov. 23., P,
16:30):
On 11/23/2018 09:54 PM, szmate1618 wrote:
Dear list members,
I have the following problem, with which I'd like to request your aid:
Currently, at version 3.25.2, SQLite only has a limited support for
alter
table. E.g. you cannot change the datatype (type affinity) of a column,
or
drop a column.
The usual workaround is to create a new table with the desired schema,
fill
it with data from the original table, drop the original table, and
rename
the new one. But what if the original table is a parent table in a
foreign
key relationship?
The official solution
<https://www.sqlite.org/lang_altertable.html#otheralter> is turning
foreign
keys off, making the changes you want, then turning foreign keys on. But
I'm slightly annoyed this cannot be done in a transaction (because
these PRAGMA
foreign_keys =s don't take effect inside of transactions, so they need
to
be issued before and after).
"PRAGMA foreign_keys = ?" is a property of the connection only, not the
database file. So what advantage would there be in including the PRAGMA
statements in the body of a transaction?
Dan.
I'd like to use deferred foreign keys instead. I have 3 queries, one of
them seems to work, the two others do not. My questions are the
following:
- Does the seemingly working query work by design? Or it's just a
fortunate(?) constellation of multiple factors, and depending on
other
tables or new data in the database it might break in the future?
Somewhat
like undefined behavior in C++?
- Why do the other ones not work? How are they different from the
first
one?
Setup
PRAGMA foreign_keys = OFF;
DROP TABLE IF EXISTS Parent;CREATE TABLE Parent(A TEXT UNIQUE,
COLUMN_TO_DROP FLOAT);INSERT INTO Parent VALUES('whatever', 0.0);
DROP TABLE IF EXISTS Child;CREATE TABLE Child(C TEXT REFERENCES
Parent(A) DEFERRABLE INITIALLY DEFERRED);INSERT INTO Child
VALUES('whatever');
PRAGMA foreign_keys = ON;
Query1 - seems to be working as intended
BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT INTO Parent SELECT *
FROM Temp;DROP TABLE Temp;COMMIT;
Query2 - create [...] as select [...] fails
BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
Parent;CREATE TABLE Parent AS SELECT * FROM Temp; -- different
from Query1CREATE UNIQUE INDEX ParentIndex on Parent(A); -- different
from Query1DROP TABLE Temp;COMMIT;
Result:
sqlite> PRAGMA foreign_key_check;
sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent(A TEXT);CREATE
UNIQUE INDEX ParentIndex on Parent(A);
sqlite> SELECT * FROM Parent;
whatever
Query3 - insert into [...] fails
BEGIN TRANSACTION;CREATE TABLE Temp (A TEXT UNIQUE); -- different
from Query1INSERT INTO Temp SELECT A FROM Parent; -- different from
Query1DROP TABLE Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT
INTO Parent SELECT * FROM Temp;DROP TABLE Temp;COMMIT;
Result:
sqlite> PRAGMA foreign_key_check;
sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent (A TEXT UNIQUE);
sqlite> SELECT * FROM Parent;
whatever
Note that PRAGMA foreign_key_check does not report any problem in any of
the cases.
I posted an identical question on StackOverflow, but no one was able to
provide any information so far. Thanks in advance!
Máté Szabó
_______________________________________________
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
