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
