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 <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 -- Richard Damon _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
