On Sun, 24 Apr 2011 23:03:03 +0200, "David L" <inta...@gmail.com>
wrote:
>Hello Sqlite-users!
>
>I have stumbled upon the fact that foreign keys are not always enforced.
>
>Specifically, that happened when I used the INSERT statement with a SELECT
>clause, which apparently caused invalid values to be inserted.
>
>How else would you explain the following?:
>
>PRAGMA foreign_keys=on;
>CREATE TABLE parent (p1 INTEGER PRIMARY KEY);
>CREATE TABLE child (c1 INTEGER PRIMARY KEY REFERENCES parent(p1));
>CREATE TABLE source (s1 INTEGER PRIMARY KEY);
>INSERT INTO parent VALUES(137);
>INSERT INTO source VALUES(-476);
>
>INSERT INTO child VALUES(-476);
>/* Gives "Error: foreign key constraint failed" as expected.
>Now let's try to insert the same value, -476, from the 'source' table */
>INSERT INTO child SELECT * FROM source;
>/* No error! */
>SELECT * FROM child;
>/* Result: -476 */
>
>
>This can't be the desired behaviour, can it?
It's wrong indeed. I wouldn't have noticed the bug,
because SELECT * is not in my vocabulary ;)
It works as intended when you name the column you need.
PRAGMA foreign_keys=on;
CREATE TABLE parent (p1 INTEGER PRIMARY KEY);
CREATE TABLE child (c1 INTEGER PRIMARY KEY
REFERENCES parent(p1));
CREATE TABLE source (s1 INTEGER PRIMARY KEY);
INSERT INTO parent VALUES(137);
INSERT INTO source VALUES(-476);
INSERT INTO child VALUES(-476);
Error: near line 8: foreign key constraint failed
INSERT INTO child SELECT s1 FROM source;
Error: near line 9: foreign key constraint failed
SELECT * FROM child;
<no result>
--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
