Thread: Create unique index or constraint on part of a column

Hi:

Is there anyway to create a unique index or constraint on part of a column?

Something like this, but something that works ;-)

  ALTER TABLE invoices
  ADD CONSTRAINT cons UNIQUE (EXTRACT(YEAR FROM invoice_date), innvoice_number);

Thanks for any help.
Ruben,

Ruben Blanco wrote on 08.03.2011 00:30:
> Hi:
>
> Is there anyway to create a unique index or constraint on part of a column?
>
> Something like this, but something that works ;-)
>
>    ALTER TABLE invoices
>    ADD CONSTRAINT cons UNIQUE (EXTRACT(YEAR FROM invoice_date), innvoice_number);
>
> Thanks for any help.
> Ruben,

CREATE UNIQUE INDEX idx_cons ON invoices (EXTRACT(YEAR FROM invoice_date), innvoice_number);

The only difference to a unique constraint is, that it cannot be used as the target of a foreign key constraint.

Regards
Thomas

On Mon, 2011-03-07 at 23:30 +0000, Ruben Blanco wrote:
> Hi:
>
> Is there anyway to create a unique index or constraint on part of a
> column?
>
> Something like this, but something that works ;-)
>
>   ALTER TABLE invoices
>   ADD CONSTRAINT cons UNIQUE (EXTRACT(YEAR FROM invoice_date),
> innvoice_number);

CREATE UNIQUE INDEX invoices_constraint_idx ON invoices
  (EXTRACT(YEAR FROM invoice_date), invoice_number);

Regards,
    Jeff Davis