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? -- Cecil Westerhof _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
