TODO list request: FK to unique expression indexes
Folks,
Since it's too late to look at this for 8.4, can the following go on the
TODO list?
Referential Integrity
[] Allow creation of FKs targeting unique expression indexes on the
referenced table. Syntax: REFERENCES <reftable> ( ( column expression ) )
Reason: current FK rules do not allow creating FKs to columns which are
defined as, for example, unique(lower(column)). This forces users to
either abandon RI for that table, to store duplicate data, or create
superfluous indexes.
Hmmm ... I suppose the above would require enabling expression indexes
for PKs as well, no?
--Josh Berkus
On Nov 19, 2008, at 9:12 AM, Josh Berkus wrote:
Folks,
Since it's too late to look at this for 8.4, can the following go on
the TODO list?Referential Integrity
[] Allow creation of FKs targeting unique expression indexes on the
referenced table. Syntax: REFERENCES <reftable> ( ( column
expression ) )Reason: current FK rules do not allow creating FKs to columns which
are defined as, for example, unique(lower(column)). This forces
users to either abandon RI for that table, to store duplicate data,
or create superfluous indexes.Hmmm ... I suppose the above would require enabling expression
indexes for PKs as well, no?
In 8.4 you should be able to get around this particular example using
citext.
Best,
David
David E. Wheeler wrote:
On Nov 19, 2008, at 9:12 AM, Josh Berkus wrote:
Folks,
Since it's too late to look at this for 8.4, can the following go on
the TODO list?Referential Integrity
[] Allow creation of FKs targeting unique expression indexes on the
referenced table. Syntax: REFERENCES <reftable> ( ( column
expression ) )Reason: current FK rules do not allow creating FKs to columns which
are defined as, for example, unique(lower(column)). This forces
users to either abandon RI for that table, to store duplicate data,
or create superfluous indexes.Hmmm ... I suppose the above would require enabling expression
indexes for PKs as well, no?In 8.4 you should be able to get around this particular example using
citext.
Yes, good idea on citext.
Allowing foreign keys to point to expression indexes seems to open a can
of worms and I am not sure there is enough demand to warrant it.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Allowing foreign keys to point to expression indexes seems to open a can
of worms and I am not sure there is enough demand to warrant it.
It does open a can of worms. I've often wanting something related,
which is the ability to make a foreign key references a PARTIAL index.
...Robert