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
