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