Re: [sqlite] Foreign key help

[x]

Good stuff Keith. One to archive.




From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Keith Medcalf <kmedc...@dessus.com>
Sent: Tuesday, November 28, 2017 4:02:35 PM
To: SQLite mailing list
Subject: Re: [sqlite] Foreign key help


And of course in the command line shell you can and should use

.lint fkey-indexes

to let you know if you are missing any indexes required for efficient 
foreign-key enforcement operations.

It will report missing indexes on the PARENT (table/columns referred to) and on 
CHILDREN (tables/columns referred from) since you need these indexes in order 
for foreign key enforcement to work without having to resort to table scans of 
the entire parent/child tables(s) on each row of an insert/update/delete 
operation affecting either a parent or child foreign key column.

If you do not have the necessary indexes defined for these relationships you 
might erroneously think that referential integrity enforcement is excessively 
expensive, and continue to use "insert/update/delete with prayers" to maintain 
referential integrity rather than have the database engine do it for you.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>On 28 Nov 2017, at 3:26pm, x <tam118...@hotmail.com> wrote:
>
>> If I have foreign keys in place but always have foreign_keys = OFF
>then one day start SQLite with foreign_keys = ON what happens? Does
>SQLite suddenly check all foreign keys and report / delete violations
>or does it leave everything as is and just enforce foreign keys from
>that point on?
>
>No.  There is no automatic check.  If you have broken a foreign key
>constraint while "foreign_keys = OFF" it may only be discovered some
>time in the future, or it may live on for as long as the database is
>used.
>
>However, you can force SQLite to check all foreign keys any time you
>want using
>
><https://sqlite.org/pragma.html#pragma_foreign_key_check>
>
>   PRAGMA foreign_key_check
>
>If it returns no rows, then there are no problems.  This check will
>work even while "foreign_keys = OFF", so you can check it before you
>turn that PRAGMA back on.
>
>Simon.
>___
>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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key help

[Keith Medcalf]

And of course in the command line shell you can and should use

.lint fkey-indexes

to let you know if you are missing any indexes required for efficient 
foreign-key enforcement operations.

It will report missing indexes on the PARENT (table/columns referred to) and on 
CHILDREN (tables/columns referred from) since you need these indexes in order 
for foreign key enforcement to work without having to resort to table scans of 
the entire parent/child tables(s) on each row of an insert/update/delete 
operation affecting either a parent or child foreign key column.

If you do not have the necessary indexes defined for these relationships you 
might erroneously think that referential integrity enforcement is excessively 
expensive, and continue to use "insert/update/delete with prayers" to maintain 
referential integrity rather than have the database engine do it for you.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>On 28 Nov 2017, at 3:26pm, x  wrote:
>
>> If I have foreign keys in place but always have foreign_keys = OFF
>then one day start SQLite with foreign_keys = ON what happens? Does
>SQLite suddenly check all foreign keys and report / delete violations
>or does it leave everything as is and just enforce foreign keys from
>that point on?
>
>No.  There is no automatic check.  If you have broken a foreign key
>constraint while "foreign_keys = OFF" it may only be discovered some
>time in the future, or it may live on for as long as the database is
>used.
>
>However, you can force SQLite to check all foreign keys any time you
>want using
>
>
>
>   PRAGMA foreign_key_check
>
>If it returns no rows, then there are no problems.  This check will
>work even while "foreign_keys = OFF", so you can check it before you
>turn that PRAGMA back on.
>
>Simon.
>___
>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

Re: [sqlite] Foreign key help

[x]

Thanks David / Simon.




From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Simon Slavin <slav...@bigfraud.org>
Sent: Tuesday, November 28, 2017 3:36:51 PM
To: SQLite mailing list
Subject: Re: [sqlite] Foreign key help

On 28 Nov 2017, at 3:26pm, x <tam118...@hotmail.com> wrote:

> If I have foreign keys in place but always have foreign_keys = OFF then one 
> day start SQLite with foreign_keys = ON what happens? Does SQLite suddenly 
> check all foreign keys and report / delete violations or does it leave 
> everything as is and just enforce foreign keys from that point on?

No.  There is no automatic check.  If you have broken a foreign key constraint 
while "foreign_keys = OFF" it may only be discovered some time in the future, 
or it may live on for as long as the database is used.

However, you can force SQLite to check all foreign keys any time you want using

<https://sqlite.org/pragma.html#pragma_foreign_key_check>

PRAGMA foreign_key_check

If it returns no rows, then there are no problems.  This check will work even 
while "foreign_keys = OFF", so you can check it before you turn that PRAGMA 
back on.

Simon.
___
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

Re: [sqlite] Foreign key help

[David Raymond]

Leaves what's there alone and just starts enforcing from when you turn it on. 
It'll only go actively looking for issues if you do a pragma foreign_key_check;

sqlite> pragma foreign_keys = off;

sqlite> create table parent (id integer primary key);

sqlite> create table child (id integer primary key, parentID int references 
parent);

sqlite> insert into parent values (1), (2);

sqlite> insert into child (parentID) values (1), (2), (3), (4);

sqlite> pragma foreign_key_check;
table|rowid|parent|fkid
child|3|parent|0
child|4|parent|0

sqlite> pragma foreign_keys = on;

sqlite> insert into parent values (3);

sqlite> update child set parentID = parentID + 1;
Error: FOREIGN KEY constraint failed

sqlite> pragma foreign_key_check;
table|rowid|parent|fkid
child|4|parent|0


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of x
Sent: Tuesday, November 28, 2017 10:27 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Foreign key help

If I have foreign keys in place but always have foreign_keys = OFF then one day 
start SQLite with foreign_keys = ON what happens? Does SQLite suddenly check 
all foreign keys and report / delete violations or does it leave everything as 
is and just enforce foreign keys from that point on?

___
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

Re: [sqlite] Foreign key help

[Simon Slavin]

On 28 Nov 2017, at 3:26pm, x  wrote:

> If I have foreign keys in place but always have foreign_keys = OFF then one 
> day start SQLite with foreign_keys = ON what happens? Does SQLite suddenly 
> check all foreign keys and report / delete violations or does it leave 
> everything as is and just enforce foreign keys from that point on?

No.  There is no automatic check.  If you have broken a foreign key constraint 
while "foreign_keys = OFF" it may only be discovered some time in the future, 
or it may live on for as long as the database is used.

However, you can force SQLite to check all foreign keys any time you want using



PRAGMA foreign_key_check

If it returns no rows, then there are no problems.  This check will work even 
while "foreign_keys = OFF", so you can check it before you turn that PRAGMA 
back on.

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

[sqlite] Foreign key help

[x]

If I have foreign keys in place but always have foreign_keys = OFF then one day 
start SQLite with foreign_keys = ON what happens? Does SQLite suddenly check 
all foreign keys and report / delete violations or does it leave everything as 
is and just enforce foreign keys from that point on?

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