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 <kmedc...@dessus.com> 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-
> >boun...@mailinglists.sqlite.org] 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
> >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
