Hi Richard,
Sorry, I missed your message before.
Look, in my example:
SQLite version 3.7.16 2012-12-20 01:15:20Enter ".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?
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
