On Fri, Dec 21, 2012 at 6:04 AM, Richard Hipp <d...@sqlite.org> wrote:
> On Thu, Dec 20, 2012 at 3:05 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) <
> lhask...@bloomberg.net> wrote:
>
> > Tested, works perfectly. Thanks!
> >
> > Two questions/observation:
> >
> > 1. Should there be a way to determine the parent key w/o looking at or
> > parsing the schema DDL commands?
> >
> > For example:
> > SQLite version 3.7.16 2012-12-20 01:15:20
> >
> > Enter ".help" for instructions
> > Enter SQL statements terminated with a ";"
> > sqlite> create table p(a, b, primary key(a, b), unique(b, a));
> > sqlite> create table c(x, y, foreign key(x, y) references p);
> > sqlite> insert into p values (1, 2);
> > sqlite> insert into c values (1, 2), (2, 1);
> > sqlite> pragma foreign_key_check(c);
> > c|2|p|0
> >
>
> The fourth column is the foreign_key_id. If you look at the output of
> PRAGMA foreign_key_list(c), you'll find all the information about parent
> table and the columns that map between parent and child, for that id.
>
>
> >
> > Now I know that the second record is in violation but I don't know what
> > key/index the foreign key actually refers to (and no other combination of
> > existing pragmas will tell me).
> >
> > 2. While I do like your API far better than what I originally proposed, I
> > found that returning no result in case of success may lead to confusion
> > since unknown pragmas behave the same way. So if I run "pragma
> > foreign_key_check;" and get empty result it can mean any of the
> following:
> >
> > 1. There are no foreign key violations - good!
> > 2. My version of SQLite does not support this pragma yet
> > 3. (In case of using the shell) I made a typo in the pragma name
> >
>
> The pragma throws an error if you enter the name of a table that does not
> exist. That handles case 3. To verify 2, that the version of SQLite you
> are using support foreign_key_check, simply use the name of a table that
> does not exist and verify that you get an error back:
>
> PRAGMA foreign_key_check('no-such-table'); --- expect an error
>
Or check sqlite_version() >= 3.7.16 ?
>
>
>
>
> >
> > While I don't have a better suggestion now, I just wanted to point it out
> > to you.
> >
> > Again thanks a lot!
> > - Levi
> >
> > ----- Original Message -----
> > From: d...@sqlite.org
> > To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN), sqlite-users@sqlite.org
> > At: Dec 19 2012 21:10:52
> >
> >
> >
> > On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) <
> > lhask...@bloomberg.net> wrote:
> >
> >> My suggestion would be to have check_integrity command verify
> referential
> >> integrity as well only if it's executed while the foreign key
> enforcement
> >> is enabled on the connection.
> >>
> >
> > The latest SQLite from trunk (not the 3.7.15.1 patch release, but the
> code
> > that is destined to become 3.7.16) has a new pragma:
> >
> > PRAGMA foreign_key_check;
> > PRAGMA foreign_key_check(TABLE);
> >
> > The second from checks all of the REFERENCES clauses in TABLE. The first
> > form checks the keys on all tables in the database.
> >
> > The result of the pragma is a table, with one row per mismatched key.
> The
> > row contains the name of the child table, the rowid of the child table,
> the
> > name of the parent table, and the "foreign key index" which is an integer
> > that describes the foreign key in PRAGMA foreign_key_list(CHILD). If the
> > foreign_key_check pragma returns an empty set, that means that all of the
> > keys are correct.
> >
> > PRAGMA foreign_key_check works regardless of whether or not foreign keys
> > are currently enabled or disabled.
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> >
> >
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
