Re: [sqlite] Foreign key integrity checking.

[Dominique Devienne]

On Fri, Jan 6, 2017 at 10:30 PM, David Raymond 
wrote:

> http://www.sqlite.org/foreignkeys.html
>
> My version of the very basic basics anyway:
> -Foreign key enforcement is turned on or off with a pragma and is a
> connection-level setting, not a property of the database itself.
> -The default enforcement is off unless a compile option is used.
> -So unless you have full control over what's accessing a database then you
> can't be sure clients are enforcing it.
> -There's a foreign_keys_check pragma which will run an integrity check and
> give you all the key violations.
> -"deferrable initially deferred" is the only way to have a deferred key,
> any other combo will be immediate. You can't change the schema for that at
> will.
> -You can force everything to be deferred if you use the defer_foreign_keys
> pragma, but you can't force anything to immediate if it started deferred.
> -Violation messages will often not tell you exactly which foreign key it
> was that was broken, though if you name your constraints it can help.
> -In keeping track of deferred foreign keys basically a tally of how many
> keys were broken vs fixed is kept. If things don't come out even it can't
> tell you where the problem record was, only that there was at least one
> problem somewhere.
> -A unique index has to be kept on the target (parent) field(s). Optionally
> a regular one on the source (child) fields speeds up enforcement checks.


Great summary David!

I'd just add the new recently announced ".lint fkey-indexes"
from the shell tool (cf http://sqlite.org/releaselog/3_16_2.html),
which find instances of un-indexed FK child column(s).

This is a classic in Oracle, where such unindexed FKs can easily lead to
deadlocks.
In SQLite, it's "just" of matter of performance, since locking is always at
the db level anyway. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key integrity checking.

[David Raymond]

http://www.sqlite.org/foreignkeys.html

My version of the very basic basics anyway:
-Foreign key enforcement is turned on or off with a pragma and is a 
connection-level setting, not a property of the database itself.
-The default enforcement is off unless a compile option is used.
-So unless you have full control over what's accessing a database then you 
can't be sure clients are enforcing it.
-There's a foreign_keys_check pragma which will run an integrity check and give 
you all the key violations.
-"deferrable initially deferred" is the only way to have a deferred key, any 
other combo will be immediate. You can't change the schema for that at will.
-You can force everything to be deferred if you use the defer_foreign_keys 
pragma, but you can't force anything to immediate if it started deferred.
-Violation messages will often not tell you exactly which foreign key it was 
that was broken, though if you name your constraints it can help.
-In keeping track of deferred foreign keys basically a tally of how many keys 
were broken vs fixed is kept. If things don't come out even it can't tell you 
where the problem record was, only that there was at least one problem 
somewhere.
-A unique index has to be kept on the target (parent) field(s). Optionally a 
regular one on the source (child) fields speeds up enforcement checks.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Michael Tiernan
Sent: Friday, January 06, 2017 4:07 PM
To: SQLite mailing list
Subject: [sqlite] Foreign key integrity checking.

I'm going to assume this has come up before so instead of asking for 
help, I'll simply ask for pointers to FAQs about some of the more 
mundane things such as ensuring foreign key integrity and checking for it.

Thanks for everyone's time.

-- 
   << MCT >> Michael C Tiernan. http://www.linkedin.com/in/mtiernan
   Non Impediti Ratione Cogatationis
   Women and cats will do as they please, and men and dogs
should relax and get used to the idea. -Robert A. Heinlein

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign key integrity checking.

[Michael Tiernan]

I'm going to assume this has come up before so instead of asking for 
help, I'll simply ask for pointers to FAQs about some of the more 
mundane things such as ensuring foreign key integrity and checking for it.


Thanks for everyone's time.

--
  << MCT >> Michael C Tiernan. http://www.linkedin.com/in/mtiernan
  Non Impediti Ratione Cogatationis
  Women and cats will do as they please, and men and dogs
   should relax and get used to the idea. -Robert A. Heinlein

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users