XPost: comp.databases.oracle.misc, comp.databases.oracle.server   
   From: mcstockX@Xenquery   
      
   "Agoston Bejo"  wrote in message   
   news:cl8jd0$f4p$1@news.caesar.elte.hu...   
   | See the answer below.   
   |   
   | "Mark C. Stock"  wrote in message   
   | news:zsqdnU_QrsleJurcRVn-vA@comcast.com...   
   | > "Agoston Bejo"  wrote in message   
   | > news:cl8ba7$d04$1@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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)