Unique constraint

[Ravala]

In table we have order_id (number) and ship_status (varchar2(1)). I'm trying to create Unique as
order_id and status.
If status null the same order_id can be in table a few times, but if status 'V' it should be unique.
Can I do that thru oracle.

Thanks.

 

[SantaMufasa]

Ravala,

Sorry, but Oracle will raise a unique-constraint error:

create table ravala (order_id number, ship_status varchar2(1),
   constraint order_id_ship_status_UK unique (order_id, ship_status));

Table created.

insert into ravala values (1,null);

1 row created.

insert into ravala values (1,null);
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.ORDER_ID_SHIP_STATUS_UK) violated

Oracle's logic on this is, "Since status IS NULL and we cannot compare to NULL, we will ignore status and check uniqueness based strictly upon the known value(s), "ORDER_ID". Since there already is a value "1", in order_id, let's throw an unique-constraint error."

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:55 (11Oct04) UTC (aka "GMT" and "Zulu"), 11:55 (11Oct04) Mountain Time)