Re: Unique constraint and NULL values

"Agoston Bejo" <gusz1_at_freemail.hu> wrote in message news:cl8jd0$f4p$1_at_news.caesar.elte.hu...
| See the answer below.
|
| "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
| news:zsqdnU_QrsleJurcRVn-vA_at_comcast.com...
| > "Agoston Bejo" <gusz1_at_freemail.hu> wrote in message
| > news:cl8ba7$d04$1_at_news.caesar.elte.hu...
| > | I want to enforce such a constraint on a column that would ensure that
| the
| > | values be all unique, but this wouldn't apply to NULL values. (I.e.
| there
| > | may be more than one NULL value in the column.)
| > | How can I achieve this?
| > | I suppose I would get the most-hated "table/view is changing,
| > | trigger/function may not see it" error if I tried to write a trigger
| that
| > | checks the uniqueness of non-null values upon insert/update.
| > |
| > |
| >
| > did you try a standard UNIQUE constraint on the column?
|
|
| Yes, I did, and (1, null) and (1, null) couldn't be inserted twice. The
| Oracle version I'm currently using (or to be more exact forced to use) is
| 8.1.7. Maybe in later versions this was corrected, I don't know. Here,
when
| I tried it, it worked the way I described in my original post.
|
| >
| > unlike SQL-Server (unless they've changed it since I last worked on it),
| > Oracle processes null values properly in this scenario (i.e., one NULL
| value
| > is never consider equal to another NULL value)
| >
| > ++ mcs
| >
| >
|
|

well, that's different than what you posted -- you want to enforce a constraint on a pair of columns, not on a single column

what i've done in cases like this is create a 'shadow table' (my term, not oracle's) that contains the two values, plus the primary key, just for rows where both values are NOT NULL -- i put the 2-column UNIQUE constraint on the shadow table instead of the original table, and then use a DML trigger on the original table to maintain the shadow table -- any constraint violations on the shadow table will propagate (through the trigger) to the original table

(a variation of this technique also allows DRI across database links, assuming the links are reliable)

++ mcs Received on Thu Oct 21 2004 - 10:24:19 CDT