Re: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

[Dan Kennedy]

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;

  if( [PRAGMA foreign_key_check]=="ok" ) COMMIT else ROLLBACK;
  PRAGMA foreign_keys = 1;


Dan.





Thanks in advance,
Máté

szmate1618  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  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
 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 

Re: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

[szmate1618]

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?

Thanks in advance,
Máté

szmate1618  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  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
>> >  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 * 

Re: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

[szmate1618]

>"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  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
> >  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
>

Re: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

[Dan Kennedy]

On 11/23/2018 10:47 PM, Thomas Kurz wrote:

To what I've learned so far, SQlite stores all data "as is" into any column 
regardless of the column declaration. The affinity only matters upon reading, am I 
correct? If so, would it be a big deal implementing ALTER TABLE ALTER COLUMN?


Affinity changes are applied before data is written to the database. As 
you say though, implementing ALTER TABLE to change the type would be 
easier if they were not.


Dan.






- Original Message -
From: Dan Kennedy 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Friday, November 23, 2018, 16:30:12
Subject: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred 
foreign key relationship

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

Re: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

[Thomas Kurz]

To what I've learned so far, SQlite stores all data "as is" into any column 
regardless of the column declaration. The affinity only matters upon reading, 
am I correct? If so, would it be a big deal implementing ALTER TABLE ALTER 
COLUMN?


- Original Message - 
From: Dan Kennedy 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Friday, November 23, 2018, 16:30:12
Subject: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred 
foreign key relationship

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

Re: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

[Dan Kennedy]

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
 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] Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

[szmate1618]

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
 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).

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