If I read http://www.firebirdsql.org/manual/nullguide-keys.html correctly, you can't. UNIQUE keys will allow multiple compound keys with all columns in the key NULL, but combinations containing non-NULL and NULL columns are considered unique and you cannot add more than one.

Mark

> I have a table that contains (among others) the following columns:
>
> item ID, BIGINT, not null
> supplier ID, BIGINT, not null
> supplier code, varchar.
>
> the last column is the code used by the supplier for a given item. It
> can be null if the user does not know it, or if he does not care.
> Obviously the supplier code must be unique for each supplier, so I tried
> to add an UNIQUE constraint to the last two columns, but it did not
> work. If I have many rows without supplier code I have the same supplier
> id in each row, and the supplier code is NULL. Firebird treats these
> rows as duplicate and I cannot create the constraint.
>
> How can I force uniqueness for supplier id and supplier code, ignoring
> null supplier codes?
>
> Thanks in advance.
>
> Fulvio Senore
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>

--
Neu: GMX De-Mail - Einfach wie E-Mail, sicher wie ein Brief!
Jetzt De-Mail-Adresse reservieren: http://portal.gmx.net/de/go/demail