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
>
> 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
