asked on

How do I create a unique constraint against 2 columns and nulls included

Hi,
How do I create a unique constraint against 2 columns and nulls included
I found it before but can't remember the syntax..

      alter table "DB_BASIC_PARM_USE" add constraint
      "DB_BASIC_PARM_USE_CON" unique ("PROJECT_ID",nvl(MY_NUM,-1))
      /
I want
insert into mytable project_id 1, my_num 2 --- inserted successfully
insert into mytable project_id 1, my_num 2 should cause constraint violation
insert into mytable project_id 1, my_num null --- inserted successfully ALLOWS ONLY ONE NULL COMBINATION
insert into mytable project_id 1, my_num null should FAIL cause constraint violation
insert into mytable project_id 1, my_num 3 --- inserted successfully
insert into mytable project_id 1, my_num 4 --- inserted successfully
insert into mytable project_id 1, my_num null should FAIL cause constraint violation
insert into mytable project_id 2, my_num null --- inserted successfully ALLOWS ONLY ONE NULL COMBINATION

thank you,
Bill

Pawan Kumar

Pls try like..

create unique index t_idx on "DB_BASIC_PARM_USE_CON"
( 
case when PROJECT_ID is not null then PROJECT_ID end, 
case when MY_NUM is not null then MY_NUM end 
);

Open in new window

BILL Carlisle

ASKER

Beautiful! thank you..

Pawan Kumar

Welcome !! Add SQL and Query syntax for future reads

BILL Carlisle

ASKER

fast too!

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BILL Carlisle

ASKER

Hi Slightwv,

Thank you, just verified.. I though it may allow more than one null.. but I should have tested it out ! thx again.

Bill

BILL Carlisle

ASKER

Actually to not misguide others the Accepted Solution should be changed..

Pawan Kumar

@Author-
So, you are saying that solution you choose (Accepted Solution) did not work ? If not then why you have accepted it. ? If that worked why do you want to change it now ? Tomorrow you may get more solution - So you will change again ?

Geert G

Pawan, you didn't check your solution ... it's wrong

As Slightwv indicated:
At times fast isn't the best and I believe unnecessary.

Pawan Kumar

Did you check the next statement from Slightwv.

Unique indexes allow nulls so I think this produces the same results.

Also its authors duty to verify before selecting the answer. Although if you see my comments history I always put tested output but every time it is not possible.

Geert G

slightwv didn't give a completely accurate phrase.

i'll rephrase it:
A unique indexe on 1 column allows exactly 1 null

BILL Carlisle

ASKER

I guess I'm suppose to respond again, being prompted by emails. :)

I am using a normal unique index. It allows only one of the same combination or if my second column is null it allows only one null with each unique value in column one... just the way I want it. Column one is a "not null" column.

Thank you for your help!
BillC.

slightwv (䄆 Netminder)

Yes, you need to properly close your question by accepting the answer that solved your problem.

Based on your post in #a42298553 you needed to change your answer.

The original post you accepted was the same one you accepted now #a42293234.

The "normal unique index" wasn't suggested by Pawan so why are you accepting that again?

slightwv (䄆 Netminder)

I propose accept: https:#a42293251