On Sun, 26 Jun 2011 18:37:46 +0100, Simon Slavin
<slav...@bigfraud.org> wrote:
>
>On 26 Jun 2011, at 12:03pm, Cecil Westerhof wrote:
>
>> Because SQLite is not a server, it is possible that someone removes a record
>> that should not be removed because of a foreign key constraint. How to check
>> if a database is still correct? There is no check after:
>> PRAGMA FOREIGN_KEYS = ON;
>> I checked.
>
> SQLite incorporates locking as part of its design.
> Rather than locking each TABLE individually,
> SQLite locks the entire database at once.
> In fact that's the only kind of locking it ever does.
> So removing a row from one TABLE will lock all other TABLEs,
> including the ones it's a FOREIGN KEY for.
I think Cecil was referring to the fact that some program can
forget to switch foreign_keys to on, and delete rows that are
referenced, or change primary keys.
So, his question is not about locking, but about verifying all
references are still pointing to existing rows.
To answer that question, suppose the following schema:
CREATE TABLE T1 (
id1 INTEGER PRIMARY KEY NOT NULL,
contents TEXT
);
CREATE TABLE T2 (
id2 INTEGER PRIMARY KEY NOT NULL,
id1 INTEGER REFERENCES T1(id1)
ON UPDATE CASCADE ON DELETE CASCADE
);
Then one could detect missing keys in T1 with:
SELECT DISTINCT T2.id1
FROM T2
LEFT OUTER JOIN T1 ON T2.id1 == T1.id1
WHERE T1.id1 IS NULL
ORDER BY T2.id1
);
(untested)
--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
