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:[email protected]] On
Behalf Of Clemens Ladisch
Sent: Wednesday, March 1, 2017 9:58 AM
To: [email protected]
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
