SQL & PL/SQL

I'm trying to find a simple solution in enforcing a quasi unique key constraint within Oracle. Simply stated I have Table A that has 3 columns. COL_A is the primary key and I've create a unique key constraint on COL_B & COL_C. All columns have the same data type. I know that Oracle permits me to have one of the 2 unique columns as NULL however it complains if I try to create another row with a identical value for COL_B and a NULL value for COL_C which is what I need.

Example of table inserts

Table A

COL_1 COL_2 COL_3 Results

1 1 1 ok
2 1 2 fails
3 2 null ok
4 null 3 ok
5 null 3 fails


Ideally, I'd like to be able to have both PK 4 and PK 5 coexist however the UK is violated understandably.

Alternatively I could probably forego the UK constraints and just develop a trigger to verify uniqueness and allow the creation of PK 4 & PK 5

Any help/suggestions would be greatly appreciated.


thanks