Re: [sqlite] Order of ON DELETE CASCADE specified in SQLite?

[Dominique Devienne]

On Wed, Mar 13, 2013 at 7:36 PM, Dan Kennedy  wrote:
> On 03/14/2013 12:09 AM, Dominique Devienne wrote:
>> I stumbled upon
>>
http://stackoverflow.com/questions/60168/in-what-order-are-on-delete-cascade-constraints-processed
>>
>> Is this just happenstance in this case or will it work every-time there
are
>> similar "pseudo cycles" that can be "broken" by processing the ON DELETE
>> CASCADE in a given order?
>>
>> If it works every-time, what's the underlying mechanism that guarantees
>> that? Some kind of topological sort between tables based on foreign keys?
>> Or perhaps the fact that FKs are enforced "lazily", after all rows have
>> been deleted?
>
> In this case, the statement deletes a row from the parent
> table. Which increments the counter by 2, as there are now
> 2 child rows without a parent. The ON DELETE CASCADE logic
> causes it to delete the row from the uncle table, which
> decrements the counter. Then the same again for the child
> table. The counter is then zero and the statement is committed.

Makes perfect sense. Thank you for this precision. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of ON DELETE CASCADE specified in SQLite?

[Dan Kennedy]

On 03/14/2013 12:09 AM, Dominique Devienne wrote:

I stumbled upon
http://stackoverflow.com/questions/60168/in-what-order-are-on-delete-cascade-constraints-processedand
tried a simplified version in SQLite3, which appears to work fine (see
below).

Is this just happenstance in this case or will it work every-time there are
similar "pseudo cycles" that can be "broken" by processing the ON DELETE
CASCADE in a given order?

If it works every-time, what's the underlying mechanism that guarantees
that? Some kind of topological sort between tables based on foreign keys?
Or perhaps the fact that FKs are enforced "lazily", after all rows have
been deleted?


I don't see any reason this won't work in SQLite.

During statement execution, SQLite uses a single counter to
keep track of whether or not it should raise an FK constraint
error instead of committing the results once the statement
has finished. The counter is incremented each time an FK
violation is introduced into the db, and decremented each time
one is removed. If the counter is greater than zero when the
statement is done executing, throw an error.

In this case, the statement deletes a row from the parent
table. Which increments the counter by 2, as there are now
2 child rows without a parent. The ON DELETE CASCADE logic
causes it to delete the row from the uncle table, which
decrements the counter. Then the same again for the child
table. The counter is then zero and the statement is committed.


This 4 years old SO post indicated both MySQL(InnoDB) and PostgreSQL failed
a similar test.


Surely they pass now though...





Just curious to know this is a behavior I can rely on or not.

Thanks, --DD

C:\Users\DDevienne>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma foreign_keys=ON;
sqlite> pragma foreign_keys;
1
sqlite> create table parent (id text primary key);
sqlite> create table child  (id text primary key,
...> parent text references parent(id) on delete cascade);
sqlite> create table uncle  (id text primary key,
...> parent text references parent(id) on delete cascade,
...> child  text references  child(id) on delete restrict);
sqlite> insert into parent values ('daddy');
sqlite> insert into child  values ('cindy', 'daddy');
sqlite> insert into uncle  values ('bobby', 'daddy', 'cindy');
sqlite> .header on
sqlite> select * from parent;
id
daddy
sqlite> select * from child;
id|parent
cindy|daddy
sqlite> select * from uncle;
id|parent|child
bobby|daddy|cindy
sqlite> delete from parent where id = 'daddy';
sqlite> select * from parent;
sqlite> select * from child;
sqlite> select * from uncle;
___
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

[sqlite] Order of ON DELETE CASCADE specified in SQLite?

[Dominique Devienne]

I stumbled upon
http://stackoverflow.com/questions/60168/in-what-order-are-on-delete-cascade-constraints-processedand
tried a simplified version in SQLite3, which appears to work fine (see
below).

Is this just happenstance in this case or will it work every-time there are
similar "pseudo cycles" that can be "broken" by processing the ON DELETE
CASCADE in a given order?

If it works every-time, what's the underlying mechanism that guarantees
that? Some kind of topological sort between tables based on foreign keys?
Or perhaps the fact that FKs are enforced "lazily", after all rows have
been deleted?

This 4 years old SO post indicated both MySQL(InnoDB) and PostgreSQL failed
a similar test.

Just curious to know this is a behavior I can rely on or not.

Thanks, --DD

C:\Users\DDevienne>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma foreign_keys=ON;
sqlite> pragma foreign_keys;
1
sqlite> create table parent (id text primary key);
sqlite> create table child  (id text primary key,
   ...> parent text references parent(id) on delete cascade);
sqlite> create table uncle  (id text primary key,
   ...> parent text references parent(id) on delete cascade,
   ...> child  text references  child(id) on delete restrict);
sqlite> insert into parent values ('daddy');
sqlite> insert into child  values ('cindy', 'daddy');
sqlite> insert into uncle  values ('bobby', 'daddy', 'cindy');
sqlite> .header on
sqlite> select * from parent;
id
daddy
sqlite> select * from child;
id|parent
cindy|daddy
sqlite> select * from uncle;
id|parent|child
bobby|daddy|cindy
sqlite> delete from parent where id = 'daddy';
sqlite> select * from parent;
sqlite> select * from child;
sqlite> select * from uncle;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users