1 2 3 Previous Next 34 Replies Latest reply: Dec 23, 2012 7:54 AM by sql_coder RSS

    insertion of null values in unique constraint column

    sharavs
      Hi,
      I have a column with unique constraint but it is accepting more NULL VALUES ;;


      CAN U GIVE ME THE REASON ....
        • 1. Re: insertion of null values in unique constraint column
          ranit B
          Because 'NULL' is never equal to anything... Not even to another NULL.
          Every NULL is unique

          Also , Unique constraint allows NULLs.
          with xx as(
              select 1 x, 1 y from dual union all
              select 1 x, NULL y from dual union all
              select NULL x, 1 y from dual union all
              select NULL x, NULL y from dual 
          )
          SELECT x, y, 
                      CASE WHEN x = y 
                      THEN 
                          'Equal' 
                      ELSE 
                          'Not - Equal' 
                      END 
           FROM xx;
          gives
          1     1     Equal
          1          Not - Equal
               1     Not - Equal
                    Not - Equal
          Hope this Helps.
          Ranit B.


          Edited by: ranit B on Dec 20, 2012 4:09 PM
          -- added code
          • 2. Re: insertion of null values in unique constraint column
            971895
            Unique key value accept NULL values...
            • 3. Re: insertion of null values in unique constraint column
              jeneesh
              ranit B wrote:
              Every NULL is unique
              Thats not very correct..
              create table test_tab(c1 number ,c2 number unique);
              
              insert into test_tab values(1,null);
              
              1 rows inserted.
              
              insert into test_tab values(1,null);
              
              1 rows inserted.
              
              select *
              from test_tab;
              
              
              C1 C2
              -- --
               1    
               1   
              
              
              select distinct *
              from test_tab;
              
              C1 C2
              -- --
               1    
              • 4. Re: insertion of null values in unique constraint column
                jeneesh
                Actually the behavior of NULL in ORACLE is some times strange..

                null values unique key
                • 5. Re: insertion of null values in unique constraint column
                  ranit B
                  Then, why does my workout shows the last statement as 'Not-Equal' ??

                  Could you please explain the underlying concept?
                  • 6. Re: insertion of null values in unique constraint column
                    Solomon Yakobson
                    ranit B wrote:
                    Then, why does my workout shows the last statement as 'Not-Equal' ??
                    Because you are trying to apply TRUE/FALSE logic while Oracle uses TRUE/FALSE/UNKNOWN logic:
                    SQL> with xx as(
                      2      select 1 x, 1 y from dual union all
                      3      select 1 x, NULL y from dual union all
                      4      select NULL x, 1 y from dual union all
                      5      select NULL x, NULL y from dual
                      6  )
                      7  SELECT x, y,
                      8              CASE WHEN x = y
                      9              THEN
                     10                  'Equal'
                     11              WHEN x != y
                     12              THEN
                     13                  'Not - Equal'
                     14              ELSE
                     15                  'Unknown'
                     16              END
                     17   FROM xx;
                    
                             X          Y CASEWHENX=Y
                    ---------- ---------- -----------
                             1          1 Equal
                             1            Unknown
                                        1 Unknown
                                          Unknown
                    
                    SQL>
                    SY.
                    • 7. Re: insertion of null values in unique constraint column
                      ranit B
                      Thanks SY.

                      In that case, if comparing to 'NULL' will never be possible right?
                      Then, how it is applying DISTINCT properly and able to group the records ???
                      • 8. Re: insertion of null values in unique constraint column
                        Solomon Yakobson
                        When you use GROUP BY (and DISTINCT is just a variation of it) Oracle takes GROUP BY column(s) of a row and decides what group that row belongs to. If column value is NULL, row belong to a group where grouping value is unknown. So for NULL group we can't say all rows within that group have same value. All we know is all rows within NULL group have unknown value. Also, aggregations (sum, max, min avg...) skip rows where to be aggregated expression is null. Only count(<font size=3 color=red>*</font>) counts NULLs. Anyway, for example:
                        SQL> select distinct comm from emp
                          2  /
                        
                              COMM
                        ----------
                        
                              1400
                               500
                               300
                                 0
                        tells us table emp has row where column comm value is either 0, 300,500, 1400 or unknown (NULL).

                        SY.
                        • 9. Re: insertion of null values in unique constraint column
                          ranit B
                          Thanks SY... It was really helpful.

                          +"Strange Oracle"+
                          • 10. Re: insertion of null values in unique constraint column
                            vijayrsehgal-Oracle
                            aggregate functions (sum, avg, max, min) ignore NULLs but count still takes care of it, group by and order by put NULLs together.
                            select comm,count(*) from emp group by comm;
                            
                            COMM|COUNT(*)
                            |11
                            300|1
                            1400|1
                            500|1
                            0|1
                            
                            
                            select comm,count(*) from emp group by comm order by comm
                            
                            COMM|COUNT(*)
                            0|1
                            300|1
                            500|1
                            1400|1
                            |11
                            here with count(*) value 11 the comm is null.
                            • 11. Re: insertion of null values in unique constraint column
                              sql_coder
                              vijays_user10302525 wrote:
                              aggregate functions (sum, avg, max, min) ignore NULLs but count still takes care of it, group by and order by put NULLs together.
                              err no. COUNT(columnname) will ignore NULL values as well. COUNT(*) is a special case not to count a value inside a column, but to count the rows. and yes NULL is strange in ORACLE, by default other dbms wont allow more then one NULL value on a column with a unique constraint.

                              ikrischer
                              • 12. Re: insertion of null values in unique constraint column
                                6363
                                Ikrischer wrote:
                                and yes NULL is strange in ORACLE, by default other dbms wont allow more then one NULL value on a column with a unique constraint.
                                So other DBMS treat NULL as being equal to NULL which is obviously completely incorrect as opposed to Oracle which is only partially incorrect.
                                • 13. Re: insertion of null values in unique constraint column
                                  sql_coder
                                  3360 wrote:
                                  So other DBMS treat NULL as being equal to NULL which is obviously completely incorrect as opposed to Oracle which is only partially incorrect.
                                  well partially incorrect can be more confusing then completely incorrect, because you dont have a straight line ^^

                                  ikrischer
                                  • 14. Re: insertion of null values in unique constraint column
                                    6363
                                    Ikrischer wrote:
                                    3360 wrote:
                                    So other DBMS treat NULL as being equal to NULL which is obviously completely incorrect as opposed to Oracle which is only partially incorrect.
                                    well partially incorrect can be more confusing then completely incorrect, because you dont have a straight line ^^
                                    Agreed, though I still prefer being partially correct to completely wrong.
                                    1 2 3 Previous Next