On Wed, Dec 12, 2012 at 10:01 PM, Jos Groot Lipman <donts...@home.nl> wrote: > When you enable foreign keys *after* you insert records, no guarantee is > given by SQLite that the foreign key constraints are fulfilled. This may not > be what most users would want or expect but it certainly not a corrupt > database by SQLite standards.
I guess one thing I don't understand in this thread is why PRAGMA foreign_keys = ON is *not* persistent with the DB file itself, just like the page size for example. Apologies if this has been discussed before, I'm newly subscribed*, but it seems to me that if the DB creator/owner intends to enforce relational integrity, that should be part of the data, otherwise anyone can come and mess up the data with no regard for the constraints, as demonstrated below. The second session *should* fail to insert (1,1) into the child table IMHO. Relying on all clients to issue the PRAGMA foreign_keys = ON (explicitly and via compile-time switch to default it to ON) is just too brittle and ignores humans natural tendency to make mistakes. If someone explicitly disables the constraints integrity, I guess that's OK, but like others in this thread, I think that trying to re-enable it later should fail if the current DB state violates the FK constraints. What am I missing? Thanks, --DD * is the reason for the non-persistent FK enforcement the fixed SQLite3 DB file format, thus the talk about SQLite4? C:\Users\DDevienne>sqlite3 testfk.db SQLite version 3.7.11 2012-03-20 11:35:50 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> sqlite> PRAGMA foreign_keys = ON; sqlite> create table parent (id int primary key); sqlite> create table child (id int primary key, parent int references parent(id)); sqlite> insert into child values (0, 0); Error: foreign key constraint failed sqlite> insert into parent values (0); sqlite> insert into child values (0, 0); sqlite> select * from parent; 0 sqlite> select * from child; 0|0 sqlite> .q C:\Users\DDevienne>sqlite3 testfk.db SQLite version 3.7.11 2012-03-20 11:35:50 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> sqlite> insert into child values (1, 1); sqlite> select * from parent; 0 sqlite> select * from child; 0|0 1|1 sqlite> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
