Re: [sqlite] Foreign Key constraint problem while dropping tables inside transaction

[Григорий Григоренко]

Понедельник, 13 мая 2013, 17:03 +01:00 от Simon Davies 
:
>On 13 May 2013 16:52, Simon Slavin < slav...@bigfraud.org > wrote:
>>
>> On 13 May 2013, at 3:54pm, Григорий Григоренко < grigore...@mail.ru > wrote:
>>
>>> sample database is:
>>>
>>> PRAGMA FOREIGN_KEYS=1;
>>> CREATE TABLE cat(id INTEGER PRIMARY KEY, name);
>>> INSERT INTO cat VALUES (1, 'Alice');
>>> CREATE TABLE owner(pet INTEGER REFERENCES cat(id));
>>> INSERT INTO owner VALUES(1);
>>>
>>> This script fails to drop tables with  'foreign key constraint failed':
>>>
>>> SAVEPOINT edit;
>>> PRAGMA FOREIGN_KEYS=0;
>>> DROP TABLE cat;
>>> DROP TABLE owner;
>>> RELEASE edit;
>>> PRAGMA FOREIGN_KEYS=1;
>>
>> You are DROPping your tables in the wrong order.  The 'owner' table refers 
>> to the 'cat' table.  If you DROP 'cat' you would be left with an 'owner' 
>> table which refers to a table which doesn't exist.  Swap the order of the 
>> DROPs and your database will not risk that kind of disaster.
>>
>>> This script works OK:
>>>
>>> PRAGMA FOREIGN_KEYS=0;
>>> DROP TABLE cat;
>>> DROP TABLE owner;
>>> PRAGMA FOREIGN_KEYS=1;
>>
>> Your problem with the one with SAVEPOINT is that SQLite doesn't know when 
>> you're going to do the RELEASE.  If you left out the second DROP, then 
>> issued a RELEASE, the database would be corrupt, so SQLite issues the error 
>> message to warn you about it.  The version without the SAVEPOINT never has 
>> to worry about you doing that.
>>
>> Simon.
>
>http://www.sqlite.org/pragma.html#pragma_foreign_keys :
>
>"...foreign key constraint enforcement may only be enabled or disabled
>when there is no pending BEGIN or SAVEPOINT. "

Ups. My fault, must have read docs more carefully.
Thank you.

>
>Regards,
>Simon
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign Key constraint problem while dropping tables inside transaction

[Simon Davies]

On 13 May 2013 16:52, Simon Slavin  wrote:
>
> On 13 May 2013, at 3:54pm, Григорий Григоренко  wrote:
>
>> sample database is:
>>
>> PRAGMA FOREIGN_KEYS=1;
>> CREATE TABLE cat(id INTEGER PRIMARY KEY, name);
>> INSERT INTO cat VALUES (1, 'Alice');
>> CREATE TABLE owner(pet INTEGER REFERENCES cat(id));
>> INSERT INTO owner VALUES(1);
>>
>> This script fails to drop tables with  'foreign key constraint failed':
>>
>> SAVEPOINT edit;
>> PRAGMA FOREIGN_KEYS=0;
>> DROP TABLE cat;
>> DROP TABLE owner;
>> RELEASE edit;
>> PRAGMA FOREIGN_KEYS=1;
>
> You are DROPping your tables in the wrong order.  The 'owner' table refers to 
> the 'cat' table.  If you DROP 'cat' you would be left with an 'owner' table 
> which refers to a table which doesn't exist.  Swap the order of the DROPs and 
> your database will not risk that kind of disaster.
>
>> This script works OK:
>>
>> PRAGMA FOREIGN_KEYS=0;
>> DROP TABLE cat;
>> DROP TABLE owner;
>> PRAGMA FOREIGN_KEYS=1;
>
> Your problem with the one with SAVEPOINT is that SQLite doesn't know when 
> you're going to do the RELEASE.  If you left out the second DROP, then issued 
> a RELEASE, the database would be corrupt, so SQLite issues the error message 
> to warn you about it.  The version without the SAVEPOINT never has to worry 
> about you doing that.
>
> Simon.

http://www.sqlite.org/pragma.html#pragma_foreign_keys:

"...foreign key constraint enforcement may only be enabled or disabled
when there is no pending BEGIN or SAVEPOINT. "

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign Key constraint problem while dropping tables inside transaction

[Simon Slavin]

On 13 May 2013, at 3:54pm, Григорий Григоренко  wrote:

> sample database is:
> 
> PRAGMA FOREIGN_KEYS=1;
> CREATE TABLE cat(id INTEGER PRIMARY KEY, name); 
> INSERT INTO cat VALUES (1, 'Alice');
> CREATE TABLE owner(pet INTEGER REFERENCES cat(id)); 
> INSERT INTO owner VALUES(1);
> 
> This script fails to drop tables with  'foreign key constraint failed':
> 
> SAVEPOINT edit;
> PRAGMA FOREIGN_KEYS=0;
> DROP TABLE cat;
> DROP TABLE owner;
> RELEASE edit; 
> PRAGMA FOREIGN_KEYS=1;

You are DROPping your tables in the wrong order.  The 'owner' table refers to 
the 'cat' table.  If you DROP 'cat' you would be left with an 'owner' table 
which refers to a table which doesn't exist.  Swap the order of the DROPs and 
your database will not risk that kind of disaster.

> This script works OK:
> 
> PRAGMA FOREIGN_KEYS=0;
> DROP TABLE cat;
> DROP TABLE owner;
> PRAGMA FOREIGN_KEYS=1;

Your problem with the one with SAVEPOINT is that SQLite doesn't know when 
you're going to do the RELEASE.  If you left out the second DROP, then issued a 
RELEASE, the database would be corrupt, so SQLite issues the error message to 
warn you about it.  The version without the SAVEPOINT never has to worry about 
you doing that.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign Key constraint problem while dropping tables inside transaction

[Григорий Григоренко]

 Hi,

sample database is:

PRAGMA FOREIGN_KEYS=1;
CREATE TABLE cat(id INTEGER PRIMARY KEY, name); 
INSERT INTO cat VALUES (1, 'Alice');
CREATE TABLE owner(pet INTEGER REFERENCES cat(id)); 
INSERT INTO owner VALUES(1);

This script fails to drop tables with  'foreign key constraint failed':

SAVEPOINT edit;
PRAGMA FOREIGN_KEYS=0;
DROP TABLE cat;
DROP TABLE owner;
RELEASE edit; 
PRAGMA FOREIGN_KEYS=1;

This script works OK:

PRAGMA FOREIGN_KEYS=0;
DROP TABLE cat;
DROP TABLE owner;
PRAGMA FOREIGN_KEYS=1;


Why?

--  Григорий Григоренко
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users