SQLite

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 SELECT * FROM source;  /* Should fail, but doesn't */
SELECT * FROM child;

This issue was reported on the mailing list. A quick look suggests that the problem is that transfer optimization is not disallowing cases where the foreign key constraints do not match on source and destination.

drh added on 2011-04-24 23:02:35:
Confirmed: the problem was in the transfer optimization.

This problem has existed in all prior versions of SQLite going back version 3.6.19 (2009-10-14) when foreign key constraints were first introduced.

A work-around is to not use the "*" in the SELECT statement but instead to list the columns to be transfered:

INSERT INTO child SELECT s1 FROM source;

The work-around avoids the problem since the transfer optimization only works on INSERT INTO ... SELECT * ... statements.