On 2015/12/13 2:17 PM, Cecil Westerhof wrote:
> I am continuing with my exploration of SQLite. :-)
>
> At the moment I am working with Foreign Keys. They need to be enabled. When
> you do not do this it is possible to enter records that break the Foreign
> Key rules. Is there a way to check for this.
>
> For example in a session where Foreign Keys where not enabled I did the
> first INSERT from:
> https://www.sqlite.org/foreignkeys.html
>
> When opening the database in a session with Foreign Keys enabled, is there
> a method to find this record that breaks the rules?
No.
Foreign Key checks only happen on data changes. I don't think there
would be a point of turning off FK checks, adding non-relation data to
the tables, then turning it back on, and simply get a permanent error
condition.
You could just re-insert or update the records, which should then break
once FK checking is turned on. This next script demonstrates:
CREATE TABLE t (
ID INTEGER PRIMARY KEY,
Name TEXT
);
CREATE TABLE ct (
ID INTEGER PRIMARY KEY,
tID INTEGER REFERENCES t(ID) ON DELETE RESTRICT ON UPDATE CASCADE
);
PRAGMA foreign_keys=0;
INSERT INTO t (Name) VALUES ('John'), ('Jane'), ('Joe');
INSERT INTO ct (tID) VALUES (1), (2), (5); -- 5 is an error but
succeeds here since FK=Off
PRAGMA foreign_keys=1;
UPDATE ct SET tID=tID WHERE 1; -- This fails because of the 5
There is however no way of knowing which specific record caused the
failure if you are group-updating anything. The reason for that was
discussed at length some weeks ago on this forum, but basically the
engine "counts" the foreign key violations and then counts back down as
they get resolved throughout a transaction. If the end result is "Zero"
violations, the transaction succeeds, if however there are one or more
outstanding violations, it fails. There is no reason or rhyme to keeping
record of which one (or more) of the many possible constraints were
violated. (This might number in the millions on large tables).
