Re: Forcing uniqueness using triggers, ORA-4091

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: 1 Jan 1999 00:20:05 GMT
Message-ID: <368d06a8.2779759@netnews.worldnet.att.net>

On Wed, 30 Dec 1998 16:36:05 +0100, Brian Rasmusson <br_at_belle.dk> wrote:

>Now, varchar_col1 is optional but must be unique for number_col1.
>Normally i would have made a UNIQUE constraint on(number_col1,
>varchar_col1) but this cannot be done because varchar_col1 may be NULL
>for several rows.

I had to deal with a situation like this recently. When you have a null varchar_col1, you don't want to bother with the uniqueness check, right? I hope that's right. That's what I understood.

My solution to this problem was to create a unique constraint on a single field. That way, null values are ignored. First add a new column to your table:

number_col1	NOT NULL	NUMBER
number_col2	NOT NULL	NUMBER
number_col3			NUMBER
varchar_col1			VARCHAR(32)
number_varchar_col1	varchar2(64)

Then, create a trigger to concatenate togetther the two columns that you are interested in:

create trigger update_shadow_fields
after insert or update of your_table
for each row
begin

	:new.number_varchar_col1 := to_char(number_col1)
	|| number_Varchar_col1

end;
/

Now, the column named number_varchar_col1 will contain the concatenated value of the two fields. If either field is null, the value of number_varchar_col1 will also be null. This is because nulls propogate when used in expressions. You can create a unique constraint on number_varchar_col1. Oracle will ignore cases where the value in that field is null, so the constraint will only be enforced when both values are present.

regards,

Jonathan

p.s. you may want to use a format string in the TO_CHAR function to ensure that you always get a fixed number of digits when converting your numbers to text. Received on Thu Dec 31 1998 - 18:20:05 CST