[sqlite] Checking Foreign Keys
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).
[sqlite] Checking Foreign Keys
On 13 Dec 2015, at 12:52pm, R Smith wrote: > 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. Agreed. There is a reason to turn the checks off, however. Suppose you want to change the rows and columns of a table. Because SQLite lacks "ALTER TABLE DROP COLUMN" and "ALTER TABLE RENAME COLUMN" commands you have to create a new table and eventually move all references to it. You can'd do that without the ability to disable FOREIGN KEYs for a while. Simon.
[sqlite] Checking Foreign Keys
2015-12-13 13:53 GMT+01:00 Dominique Devienne : > On Sun, Dec 13, 2015 at 1:17 PM, Cecil Westerhof > wrote: > > > 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? > > > https://www.sqlite.org/pragma.html#pragma_foreign_key_check > ?That is very interesting information. Thanks. -- Cecil Westerhof
[sqlite] Checking Foreign Keys
2015-12-13 13:52 GMT+01:00 R Smith : > > > 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. > ?I agree, but you never know what someone else is doing. ? -- Cecil Westerhof
[sqlite] Checking Foreign Keys
On Sun, Dec 13, 2015 at 1:17 PM, Cecil Westerhof wrote: > 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? https://www.sqlite.org/pragma.html#pragma_foreign_key_check --DD
[sqlite] Checking Foreign Keys
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? -- Cecil Westerhof
