Thanks, thought about it. But I have a ROWID requirement as well due to foreign keys referring back to this table via the ROWID.
I could in theory put that just the RowId in an additional index, but then the RowId index will just carry the entire UniqueCol again - back to the same problem, just reversed. To throw the specific design out there: This is a frequent read, infrequent update table: CREATE TABLE Blah( Id Integer Primary Key, UniqueCol blob, // 20 bytes fixed ExtraCol blob, // 12 bytes fixed UNIQUE(UniqueCol) ); // ~36 bytes/row CREATE INDEX sqlite_autoindex_Resource_1 on Blah ( // implicit index UniqueCol, // 20 bytes // Id -- implicit ) // ~24 bytes/row CREATE INDEX blahIndex on Blah ( // actual needed index for workload UniqueCol, // 20 bytes ExtraCol, // 12 bytes // Id -- implicit ) // ~36 bytes/row So this 3rd index is exactly as big as the original table. I would love to get rid of at least the 2 vs. 3 redundancy here. - Deon -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Clemens Ladisch Sent: Wednesday, March 1, 2017 9:58 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Non-unique columns in unique index Deon Brewis wrote: > Is there way to add non-unique columns in a unique index? > > I would like to use the same index to enforce unique constraints, as well as > giving a covered result for other queries. > > CREATE UNIQUE INDEX indx ON Foo(UniqueCol) INCLUDE (ExtraCol) CREATE TABLE Foo ( UniqueCol PRIMARY KEY, ExtraCol, [...] ) WITHOUT ROWID; Regards, Clemens _______________________________________________ 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