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