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 <kmedc...@dessus.com> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> 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- >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
