Обсуждение: 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