Re: multiple fields index

On Wednesday 09 Apr 2003 6:09 pm, enediel wrote:
> I need a database where all text information will be kept as the users
> wrote it.
>
> This purposes includes a problem to create unique indexes with multiples
> fields, where text fields are part of the indexes.
>
> Suppose for example a table INSTITUTIONS, and other table
> CLIENTS_PER_INSTITUTION
> I need to create a unique index (nu_intitution, client_name) in the second
> table where nu_intitution is an integer and a foreign key of the first
> table, and client_name is a text.
>
> Someting like
> create index i_clients on  CLIENTS_PER_INSTITUTION (nu_intitution,
> upper(client_name)) is impossible, at least in the documentation I've read.

Well, the only issue would be with using the function in the index. You
shouldn't have a problem with (nu_intitution, client_name).

If you want to upper() or lower() the client_name, I think you'll have to
define a custom function and index on that:

CREATE INDEX i_clients on CLIENTS_PER_INSTITUTION
( my_custom_func(nu_intitution, client_name) );

Concatenate the number and lower(...) inside the custom function and return
it. There's a short section on functional indexes in the manuals.
--
  Richard Huxton