On Mon, 27 Jun 2011 07:13:14 +0200, Cecil Westerhof <cldwester...@gmail.com> wrote:
>2011/6/26 Kees Nuyt <k.n...@zonnet.nl> > >> I think Cecil was referring to the fact that some program can >> forget to switch foreign_keys to on, and delete rows that are >> referenced, or change primary keys. >> > >Yes, that is what I mend. > > >So, his question is not about locking, but about verifying all >> references are still pointing to existing rows. >> >> To answer that question, suppose the following schema: >> >> CREATE TABLE T1 ( >> id1 INTEGER PRIMARY KEY NOT NULL, >> contents TEXT >> ); >> >> CREATE TABLE T2 ( >> id2 INTEGER PRIMARY KEY NOT NULL, >> id1 INTEGER REFERENCES T1(id1) >> ON UPDATE CASCADE ON DELETE CASCADE >> ); >> >> Then one could detect missing keys in T1 with: >> >> SELECT DISTINCT T2.id1 >> FROM T2 >> LEFT OUTER JOIN T1 ON T2.id1 == T1.id1 >> WHERE T1.id1 IS NULL >> ORDER BY T2.id1 >> ); >> > >Okay, so it can only be done manually? Not necessarily. You could script it using another nice PRAGMA: sqlite> .head on sqlite> PRAGMA foreign_key_list(Statistics); id|seq|table|from|to|on_update|on_delete|match 0|0|PhysicalLinkStatuss|pl_id|pl_id|CASCADE|CASCADE|NONE 1|0|WANAccessTypes|wa_id|wa_id|CASCADE|CASCADE|NONE 2|0|LastConnectionErrors|le_id|le_id|CASCADE|CASCADE|NONE 3|0|ConnectionStatuss|cs_id|cs_id|CASCADE|CASCADE|NONE : As you see, it lists all tables and keys some table references. -- ( Kees Nuyt ) c[_] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
