Broken Foreign key

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Broken Foreign key

J Decker
https://d3x0r.org:444/temporary/broken_fk.tgz  (30k)

this has a .option.db in it and a test.sh.
It turns on foreign_keys, selects from the name_id that it says is a
forieng key violation, and inserts into option4_map.

option4_map has option_id(PK),parent_id(FK option_id),name_id(FK)
option4_name has name_id(PK), name
option4_values has option_id(FK),string

the insert says the name isn't there; in the real scenario the ID of the
name was JUST selected; as it is here... so it sort of had to be there;
although technically the insert happens on a new connection (after pragma
foreign_keys=on init runs)


sqlite3 --echo .option.db << EOF
pragma foreign_keys=on;
select * from option4_name where
name_id='5e49af20-e621-11e8-9a4c-0cc47a7f9351';
.print Insert into option4_map(option_id,parent_option_id,name_id) values
('366a676a-e626-11e8-a7de-0cc47a7f9351','b2b9feae-315d-11e7-b998-0cc47a7f9350','5e49af20-e621-11e8-9a4c-0cc47a7f9351');
Insert into option4_map(option_id,parent_option_id,name_id) values
('366a676a-e626-11e8-a7de-0cc47a7f9351','b2b9feae-315d-11e7-b998-0cc47a7f9350','5e49af20-e621-11e8-9a4c-0cc47a7f9351');
EOF


I tested with sqlite3   3.25.2(different linux system)   3.24.2(actual
code)    3.7.17(actual linux system)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Broken Foreign key

Keith Medcalf

parent keys MUST have unique indexes.  Add the missing indexes.
child keys SHOULD be indexed:  UNIQUE indexes for 1:1 relationships, non-unique indexes for 1:N relationships

put unique indexes on your parents and all will work just fine.

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

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of J Decker
>Sent: Sunday, 11 November, 2018 20:25
>To: General Discussion of SQLite Database
>Subject: [sqlite] Broken Foreign key
>
>https://d3x0r.org:444/temporary/broken_fk.tgz  (30k)
>
>this has a .option.db in it and a test.sh.
>It turns on foreign_keys, selects from the name_id that it says is a
>forieng key violation, and inserts into option4_map.
>
>option4_map has option_id(PK),parent_id(FK option_id),name_id(FK)
>option4_name has name_id(PK), name
>option4_values has option_id(FK),string
>
>the insert says the name isn't there; in the real scenario the ID of
>the
>name was JUST selected; as it is here... so it sort of had to be
>there;
>although technically the insert happens on a new connection (after
>pragma
>foreign_keys=on init runs)
>
>
>sqlite3 --echo .option.db << EOF
>pragma foreign_keys=on;
>select * from option4_name where
>name_id='5e49af20-e621-11e8-9a4c-0cc47a7f9351';
>.print Insert into option4_map(option_id,parent_option_id,name_id)
>values
>('366a676a-e626-11e8-a7de-0cc47a7f9351','b2b9feae-315d-11e7-b998-
>0cc47a7f9350','5e49af20-e621-11e8-9a4c-0cc47a7f9351');
>Insert into option4_map(option_id,parent_option_id,name_id) values
>('366a676a-e626-11e8-a7de-0cc47a7f9351','b2b9feae-315d-11e7-b998-
>0cc47a7f9350','5e49af20-e621-11e8-9a4c-0cc47a7f9351');
>EOF
>
>
>I tested with sqlite3   3.25.2(different linux system)
>3.24.2(actual
>code)    3.7.17(actual linux system)
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Broken Foreign key

J Decker
I see.  That version of the database is missing primary key option4_name...

Why did it suddenly start failing without the primary key index when it was
working for a very long time ...
it's almost like something removed the primary key

table|option4_name|CREATE TABLE `option4_name` (`name_id` char(36) NOT NULL
PRIMARY KEY,`name` varchar(255) NOT NULL default '' UNIQUE)
index|sqlite_autoindex_option4_name_1|option4_name|3|
index|sqlite_autoindex_option4_name_2|option4_name|4|
index|name|option4_name|5|CREATE UNIQUE INDEX 'name' ON
'option4_name'('name')



On Sun, Nov 11, 2018 at 8:15 PM Keith Medcalf <[hidden email]> wrote:

>
> parent keys MUST have unique indexes.  Add the missing indexes.
> child keys SHOULD be indexed:  UNIQUE indexes for 1:1 relationships,
> non-unique indexes for 1:N relationships
>
> put unique indexes on your parents and all will work just fine.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[hidden email]] On Behalf Of J Decker
> >Sent: Sunday, 11 November, 2018 20:25
> >To: General Discussion of SQLite Database
> >Subject: [sqlite] Broken Foreign key
> >
> >https://d3x0r.org:444/temporary/broken_fk.tgz  (30k)
> >
> >this has a .option.db in it and a test.sh.
> >It turns on foreign_keys, selects from the name_id that it says is a
> >forieng key violation, and inserts into option4_map.
> >
> >option4_map has option_id(PK),parent_id(FK option_id),name_id(FK)
> >option4_name has name_id(PK), name
> >option4_values has option_id(FK),string
> >
> >the insert says the name isn't there; in the real scenario the ID of
> >the
> >name was JUST selected; as it is here... so it sort of had to be
> >there;
> >although technically the insert happens on a new connection (after
> >pragma
> >foreign_keys=on init runs)
> >
> >
> >sqlite3 --echo .option.db << EOF
> >pragma foreign_keys=on;
> >select * from option4_name where
> >name_id='5e49af20-e621-11e8-9a4c-0cc47a7f9351';
> >.print Insert into option4_map(option_id,parent_option_id,name_id)
> >values
> >('366a676a-e626-11e8-a7de-0cc47a7f9351','b2b9feae-315d-11e7-b998-
> >0cc47a7f9350','5e49af20-e621-11e8-9a4c-0cc47a7f9351');
> >Insert into option4_map(option_id,parent_option_id,name_id) values
> >('366a676a-e626-11e8-a7de-0cc47a7f9351','b2b9feae-315d-11e7-b998-
> >0cc47a7f9350','5e49af20-e621-11e8-9a4c-0cc47a7f9351');
> >EOF
> >
> >
> >I tested with sqlite3   3.25.2(different linux system)
> >3.24.2(actual
> >code)    3.7.17(actual linux system)
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Broken Foreign key

Keith Medcalf

You do not need to define a column as UNIQUE and also put a UNIQUE INDEX on it.  

create table a
(
  b unique
);

is merely syntactic sugar for

create table a
(
  b
);
create unique index sqlite_autoindex_a_1 on a (b);

so declaring something with a unique constraint on a column and also explicitly creating a unique index on that column merely creates *TWO* unique indexes on the same column (thus requiring twice the space and twice the maintenance overhead).

That is

create table a
(
  b unique
);
create unique index b_a on a (b);

is exactly the same as

create table a
(
  b
);
create unique index sqlite_autoindex_a_1 on a (b)'
create unique index b_a on a (b);


Note, PRIMARY KEY is just syntactic sugar for the UNIQUE keyword, except in the case of an INTEGER PRIMARY KEY in a rowid table (where is designates the column as an explicit rowid), or in a WITHOUT ROWID table (where PRIMARY KEY also puts a NOT NULL constraint on the column).  And of course it allows you to reference the table by name and omit the column name in the references clause of a foreign key constraint.

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

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of J Decker
>Sent: Tuesday, 13 November, 2018 04:33
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] Broken Foreign key
>
>I see.  That version of the database is missing primary key
>option4_name...
>
>Why did it suddenly start failing without the primary key index when
>it was
>working for a very long time ...
>it's almost like something removed the primary key
>
>table|option4_name|CREATE TABLE `option4_name` (`name_id` char(36)
>NOT NULL
>PRIMARY KEY,`name` varchar(255) NOT NULL default '' UNIQUE)
>index|sqlite_autoindex_option4_name_1|option4_name|3|
>index|sqlite_autoindex_option4_name_2|option4_name|4|
>index|name|option4_name|5|CREATE UNIQUE INDEX 'name' ON
>'option4_name'('name')
>
>
>
>On Sun, Nov 11, 2018 at 8:15 PM Keith Medcalf <[hidden email]>
>wrote:
>
>>
>> parent keys MUST have unique indexes.  Add the missing indexes.
>> child keys SHOULD be indexed:  UNIQUE indexes for 1:1
>relationships,
>> non-unique indexes for 1:N relationships
>>
>> put unique indexes on your parents and all will work just fine.
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says
>> a lot about anticipated traffic volume.
>>
>> >-----Original Message-----
>> >From: sqlite-users [mailto:sqlite-users-
>> >[hidden email]] On Behalf Of J Decker
>> >Sent: Sunday, 11 November, 2018 20:25
>> >To: General Discussion of SQLite Database
>> >Subject: [sqlite] Broken Foreign key
>> >
>> >https://d3x0r.org:444/temporary/broken_fk.tgz  (30k)
>> >
>> >this has a .option.db in it and a test.sh.
>> >It turns on foreign_keys, selects from the name_id that it says is
>a
>> >forieng key violation, and inserts into option4_map.
>> >
>> >option4_map has option_id(PK),parent_id(FK option_id),name_id(FK)
>> >option4_name has name_id(PK), name
>> >option4_values has option_id(FK),string
>> >
>> >the insert says the name isn't there; in the real scenario the ID
>of
>> >the
>> >name was JUST selected; as it is here... so it sort of had to be
>> >there;
>> >although technically the insert happens on a new connection (after
>> >pragma
>> >foreign_keys=on init runs)
>> >
>> >
>> >sqlite3 --echo .option.db << EOF
>> >pragma foreign_keys=on;
>> >select * from option4_name where
>> >name_id='5e49af20-e621-11e8-9a4c-0cc47a7f9351';
>> >.print Insert into option4_map(option_id,parent_option_id,name_id)
>> >values
>> >('366a676a-e626-11e8-a7de-0cc47a7f9351','b2b9feae-315d-11e7-b998-
>> >0cc47a7f9350','5e49af20-e621-11e8-9a4c-0cc47a7f9351');
>> >Insert into option4_map(option_id,parent_option_id,name_id) values
>> >('366a676a-e626-11e8-a7de-0cc47a7f9351','b2b9feae-315d-11e7-b998-
>> >0cc47a7f9350','5e49af20-e621-11e8-9a4c-0cc47a7f9351');
>> >EOF
>> >
>> >
>> >I tested with sqlite3   3.25.2(different linux system)
>> >3.24.2(actual
>> >code)    3.7.17(actual linux system)
>> >_______________________________________________
>> >sqlite-users mailing list
>> >[hidden email]
>> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Broken Foreign key

Thomas Kurz
In reply to this post by Keith Medcalf
May I ask why parent keys *must* have indexes? I don't see any correlation between enforcing a client/parent-relationship and the necessity for an index. I'm just asking to better understand. To me it is clear that the parent column is to be declared as PRIMARY KEY or UNIQUE, but why does it need to have an index?

And do I have to manually index PRIMARY KEY columns as well or are those auto-indexed?

Kind reagrds,
Thomas


----- Original Message -----
From: Keith Medcalf <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Monday, November 12, 2018, 05:15:35
Subject: [sqlite] Broken Foreign key


parent keys MUST have unique indexes.  Add the missing indexes.
child keys SHOULD be indexed:  UNIQUE indexes for 1:1 relationships, non-unique indexes for 1:N relationships

put unique indexes on your parents and all will work just fine.

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

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of J Decker
>Sent: Sunday, 11 November, 2018 20:25
>To: General Discussion of SQLite Database
>Subject: [sqlite] Broken Foreign key

>https://d3x0r.org:444/temporary/broken_fk.tgz  (30k)

>this has a .option.db in it and a test.sh.
>It turns on foreign_keys, selects from the name_id that it says is a
>forieng key violation, and inserts into option4_map.

>option4_map has option_id(PK),parent_id(FK option_id),name_id(FK)
>option4_name has name_id(PK), name
>option4_values has option_id(FK),string

>the insert says the name isn't there; in the real scenario the ID of
>the
>name was JUST selected; as it is here... so it sort of had to be
>there;
>although technically the insert happens on a new connection (after
>pragma
>foreign_keys=on init runs)


>sqlite3 --echo .option.db << EOF
>pragma foreign_keys=on;
>select * from option4_name where
>name_id='5e49af20-e621-11e8-9a4c-0cc47a7f9351';
>.print Insert into option4_map(option_id,parent_option_id,name_id)
>values
>('366a676a-e626-11e8-a7de-0cc47a7f9351','b2b9feae-315d-11e7-b998-
>0cc47a7f9350','5e49af20-e621-11e8-9a4c-0cc47a7f9351');
>Insert into option4_map(option_id,parent_option_id,name_id) values
>('366a676a-e626-11e8-a7de-0cc47a7f9351','b2b9feae-315d-11e7-b998-
>0cc47a7f9350','5e49af20-e621-11e8-9a4c-0cc47a7f9351');
>EOF


>I tested with sqlite3   3.25.2(different linux system)
>3.24.2(actual
>code)    3.7.17(actual linux system)
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Broken Foreign key

Richard Damon
My guess on the requirement on the Parent Key needing a Unique Index is
that every insert into the child field needs to verify that there IS a
corresponding value in the parent key, and it would be SO inefficient
(in general) to need to do a full table scan to verify this, that it is
required to have an appropriate index.

As has been mentioned, defining the field as PRIMARY or UNIQUE *will*
create the appropriate index, it shouldn't be also manually declared.


On 11/13/18 7:47 AM, Thomas Kurz wrote:

> May I ask why parent keys *must* have indexes? I don't see any correlation between enforcing a client/parent-relationship and the necessity for an index. I'm just asking to better understand. To me it is clear that the parent column is to be declared as PRIMARY KEY or UNIQUE, but why does it need to have an index?
>
> And do I have to manually index PRIMARY KEY columns as well or are those auto-indexed?
>
> Kind reagrds,
> Thomas
>
>
> ----- Original Message -----
> From: Keith Medcalf <[hidden email]>
> To: SQLite mailing list <[hidden email]>
> Sent: Monday, November 12, 2018, 05:15:35
> Subject: [sqlite] Broken Foreign key
>
>
> parent keys MUST have unique indexes.  Add the missing indexes.
> child keys SHOULD be indexed:  UNIQUE indexes for 1:1 relationships, non-unique indexes for 1:N relationships
>
> put unique indexes on your parents and all will work just fine.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.
>
>> -----Original Message-----
>> From: sqlite-users [mailto:sqlite-users-
>> [hidden email]] On Behalf Of J Decker
>> Sent: Sunday, 11 November, 2018 20:25
>> To: General Discussion of SQLite Database
>> Subject: [sqlite] Broken Foreign key
>> https://d3x0r.org:444/temporary/broken_fk.tgz  (30k)
>> this has a .option.db in it and a test.sh.
>> It turns on foreign_keys, selects from the name_id that it says is a
>> forieng key violation, and inserts into option4_map.
>> option4_map has option_id(PK),parent_id(FK option_id),name_id(FK)
>> option4_name has name_id(PK), name
>> option4_values has option_id(FK),string
>> the insert says the name isn't there; in the real scenario the ID of
>> the
>> name was JUST selected; as it is here... so it sort of had to be
>> there;
>> although technically the insert happens on a new connection (after
>> pragma
>> foreign_keys=on init runs)
>
>> sqlite3 --echo .option.db << EOF
>> pragma foreign_keys=on;
>> select * from option4_name where
>> name_id='5e49af20-e621-11e8-9a4c-0cc47a7f9351';
>> .print Insert into option4_map(option_id,parent_option_id,name_id)
>> values
>> ('366a676a-e626-11e8-a7de-0cc47a7f9351','b2b9feae-315d-11e7-b998-
>> 0cc47a7f9350','5e49af20-e621-11e8-9a4c-0cc47a7f9351');
>> Insert into option4_map(option_id,parent_option_id,name_id) values
>> ('366a676a-e626-11e8-a7de-0cc47a7f9351','b2b9feae-315d-11e7-b998-
>> 0cc47a7f9350','5e49af20-e621-11e8-9a4c-0cc47a7f9351');
>> EOF
>
>> I tested with sqlite3   3.25.2(different linux system)
>> 3.24.2(actual
>> code)    3.7.17(actual linux system)
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Broken Foreign key

Keith Medcalf
In reply to this post by Thomas Kurz

On Tuesday, 13 November, 2018 05:47, Thomas Kurz <[hidden email]> asked:

>May I ask why parent keys *must* have indexes? I don't see any
>correlation between enforcing a client/parent-relationship and the
>necessity for an index. I'm just asking to better understand. To me
>it is clear that the parent column is to be declared as PRIMARY KEY
>or UNIQUE, but why does it need to have an index?

It does not.  However in most database storage systems the "UNIQUE" and "PRIMARY KEY" attributes are implemented by a unique index.  If you happen to be using one of the few (if any) systems that do not implement a UNIQUE (or PRIMARY KEY, which is the same thing spelled differently (with very minor differences)) through an index, and you are willing to suffer a table scan every single time a constraint needs to verify whether or not a parent record exists, then you are free NOT to have an index and just have it be unique (and no, prayerfully unique is not sufficient -- the database system must throw an error if you attempt to violate the unique requirement)).  The same applies to indexes on the child columns of a foreign key constraint.  They do not need to be indexed either if you are willing to suffer a table scan every time the existence of a child needs to be verified.

>And do I have to manually index PRIMARY KEY columns as well or are
>those auto-indexed?

That is what is called "implementation dependent".  If you were using an implementation that did not use an index to enforce uniqueness (and I do not think there are any) then you would need to define an index yourself if you wanted one.  Since I do not think there is presently any implementation which does not use an index to enforce uniqueness, declaring a separate index merely creates a separate (and perhaps duplicate) index.

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





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Broken Foreign key

Thomas Kurz
Ok, now I understand. Thank you (and Richard) very much.


----- Original Message -----
From: Keith Medcalf <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Tuesday, November 13, 2018, 14:06:11
Subject: [sqlite] Broken Foreign key


On Tuesday, 13 November, 2018 05:47, Thomas Kurz <[hidden email]> asked:

>May I ask why parent keys *must* have indexes? I don't see any
>correlation between enforcing a client/parent-relationship and the
>necessity for an index. I'm just asking to better understand. To me
>it is clear that the parent column is to be declared as PRIMARY KEY
>or UNIQUE, but why does it need to have an index?

It does not.  However in most database storage systems the "UNIQUE" and "PRIMARY KEY" attributes are implemented by a unique index.  If you happen to be using one of the few (if any) systems that do not implement a UNIQUE (or PRIMARY KEY, which is the same thing spelled differently (with very minor differences)) through an index, and you are willing to suffer a table scan every single time a constraint needs to verify whether or not a parent record exists, then you are free NOT to have an index and just have it be unique (and no, prayerfully unique is not sufficient -- the database system must throw an error if you attempt to violate the unique requirement)).  The same applies to indexes on the child columns of a foreign key constraint.  They do not need to be indexed either if you are willing to suffer a table scan every time the existence of a child needs to be verified.

>And do I have to manually index PRIMARY KEY columns as well or are
>those auto-indexed?

That is what is called "implementation dependent".  If you were using an implementation that did not use an index to enforce uniqueness (and I do not think there are any) then you would need to define an index yourself if you wanted one.  Since I do not think there is presently any implementation which does not use an index to enforce uniqueness, declaring a separate index merely creates a separate (and perhaps duplicate) index.

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





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users