On Thu, Dec 27, 2012 at 11:43 AM, Levi Haskell (BLOOMBERG/ 731 LEXIN) <
lhask...@bloomberg.net> wrote:
> Hi Richard,
>
> Sorry, I missed your message before.
>
> Look, in my 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
> sqlite> pragma foreign_key_list(c);
> 0|0|p|x||NO ACTION|NO ACTION|NONE
> 0|1|p|y||NO ACTION|NO ACTION|NONE
> sqlite> pragma table_info(p);
> 0|a||0||1
> 1|b||0||1
> sqlite> pragma index_list(p);
> 0|sqlite_autoindex_p_2|1
> 1|sqlite_autoindex_p_1|1
>
> No pragma will give me enough info to tell whether fkey implies that c.x =
> p.a and c.y = p.b *or* c.x = p.b and c.y = p.a.
>
> Am I missing something?
>
In the latest code on the SQLite trunk, the table_info pragma has been
enhanced so that the "pk" column (the right-most column) gives the order of
the columns in the primary key.
>
> Thanks,
> - Levi
>
> ----- Original Message -----
> From: d...@sqlite.org
> To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN)
> Cc: sqlite-users@sqlite.org
> At: Dec 20 2012 23:05:20
>
>
>
> 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
>
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
