Re: How to create unique constraint on NULL columns

On 7/18/05, Dawid Kuroczko <qnex42(at)gmail(dot)com> wrote:
> On 7/18/05, Andrus <eetasoft(at)online(dot)ee> wrote:
> > I have meaningful primary key in department table (department code used
> > inside enterptise), not a surrogate number (I use meaningful primary keys
> > whenever possible).
>
> OK, so then just define the UNIQUE INDEX to be exactly what you need,
> for example:

Or better:
CREATE UNIQUE INDEX permission_unique_key (user_id,permission_id,department_id);
CREATE UNIQUE INDEX permission_uninull_key (user_id,permission_id)
WHERE department_id IS NULL;

...you may want to add WHERE department_id IS NOT NULL to the first query
(or not). Anyway -- this way these indexes could be used by searches easily
than using previous index I've sent.

Regards,
Dawid