Re: multiple fields index

On Wed, 9 Apr 2003, 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.

You'll need to generate a simple function that takes an int and a text and
generates a unique value based on those that you use in the index and
queries. If there's a known value that won't occur in client name this
could be as simple as concatenating nu_intitution and upper(client_name)
with a separator.