12 Replies Latest reply: Sep 10, 2013 9:04 AM by Frank Kulash RSS

    Unique Constraint

    940315

      can unique accept multiple null value in column or not ?

        • 1. Re: Unique Constraint
          Hoek

          Did you look it up in the Online Oracle Documentation?

          Data Integrity

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

          "Unless a NOT NULL constraint is also defined, a null always satisfies a unique key constraint. Thus, columns with both unique key constraints and NOT NULL constraints are typical. This combination forces the user to enter values in the unique key and eliminates the possibility that new row data conflicts with existing row data."

          • 2. Re: Unique Constraint
            Martin Preiss

            and if you don't want to take a look at the documentation you can check the behaviour yourself: create a test table, add a UK constraint and try to add rows with NULL values.

             

            -- 11.1.0.7

            SQL> drop table t1;

            Tabelle wurde gelöscht.

             

            SQL> create table t1 (col1 number, constraint col1_uk unique(col1));

            Tabelle wurde erstellt.

             

            SQL> insert into t1 (col1) values(null);

            1 Zeile wurde erstellt.

             

            SQL> insert into t1 (col1) values(null);

            1 Zeile wurde erstellt.

             

            SQL> insert into t1 (col1) values(1);

            1 Zeile wurde erstellt.


            SQL> insert into t1 (col1) values(1);

            insert into t1 (col1) values(1)

            *

            FEHLER in Zeile 1:

            ORA-00001: Unique Constraint (DBADMIN.COL1_UK) verletzt

            • 3. Re: Unique Constraint
              Oracle Maniac

              Well to answer you ,its YES . This is one of the difference between a primary key and a unique key constraint is that unique can accept NULLS.  Primary key is similar to Unique key with not null constraint enforced.

              • 4. Re: Unique Constraint
                Raunaq

                Yes

                • 5. Re: Unique Constraint
                  940315

                  Hi MartinPreiss,

                   

                  thanks for your response. i am still confuse UK constraint take only unique value, but it can take multiple nulls, that means two null is not same.

                  then why this query return row ?

                   

                  "select 1 from dual where null is null"

                   

                  please explain.

                  • 6. Re: Unique Constraint
                    Suri

                    Hi,

                     

                    Null is not a value. We cant compare two nulls. When you apply unique constraint Oracle will not treat two nulls as same. Thats why null can be inserted n number of times.

                     

                    Cheers,

                    Suri

                    • 7. Re: Unique Constraint
                      Paul  Horth

                      940315 wrote:

                       

                      Hi MartinPreiss,

                       

                      thanks for your response. i am still confuse UK constraint take only unique value, but it can take multiple nulls, that means two null is not same.

                      then why this query return row ?

                       

                      "select 1 from dual where null is null"

                       

                      please explain.

                       

                       

                      select 1 from dual where null=null

                      is false.

                       

                      Also false would be

                      select 1 from dual where null!=null

                      and

                      select 1 from dual where null<null

                      and

                      ...

                       

                      because null is not comparable with anything, including null.

                       

                      So, the only way to test if it is null, is using IS or IS NOT null.

                      • 8. Re: Unique Constraint
                        SomeoneElse

                        > select 1 from dual where null=null

                        > is false.

                         

                        Strictly speaking, the result isn't false but null.

                         

                        If it really were false, then this would return a row:

                         

                        SQL> select * from dual where not (null = null);

                        no rows selected

                        • 9. Re: Unique Constraint
                          Frank Kulash

                          Hi,

                           

                           

                           

                          SomeoneElse wrote:

                           

                          > select 1 from dual where null=null

                          > is false.

                           

                          Strictly speaking, the result isn't false but null...

                           

                          Strictly speaking, the result isn't NULL but UNKNOWN.

                           

                          OP: Review SQL's 3-value logic: http://docs.oracle.com/cd/E11882_01/server.112/e26088/conditions004.htm#sthref1936

                          Conditions such as "x = y" can be TRUE, FALSE or UNKNOWN.  If either x or y are NULL (including the case when both are), then "x = y" is UNKNOWN.

                          As others have said, NULL is not a value.  In fact, it's exactly the oppositie: NULL is the lack of any value.

                          • 10. Re: Unique Constraint
                            SomeoneElse

                            > Strictly speaking, the result isn't NULL nut UNKNOWN.

                             

                            But isn't NULL synonymous with UNKNOWN in this context?

                             

                            This pl/sql example returns the expected result.

                             

                            SQL> begin
                              2     if (null = null) is null then
                              3        dbms_output.put_line('result is null');
                              4     end if;
                              5  end;
                              6  /
                            result is null

                            PL/SQL procedure successfully completed.

                            • 11. Re: Unique Constraint
                              Paul  Horth

                              SomeoneElse wrote:

                               

                              > select 1 from dual where null=null

                              > is false.

                               

                              Strictly speaking, the result isn't false but null.

                               

                              If it really were false, then this would return a row:

                               

                              SQL> select * from dual where not (null = null);

                              no rows selected

                              Strictly speaking you're right.

                              Better to say a null comparison (that is not an IS or IS NOT NULL) gives a UNKNOWN value in the tri-state logic.

                               

                              The effect of this is similar to the predicate being false - that is rows are not returned.

                              • 12. Re: Unique Constraint
                                Frank Kulash

                                Hi,

                                 

                                That's right; PL/SQL uses NULL.  As of my last message, no one was talking about PL/SQL.  Since I was pointing OP towards the SQL documentation, which calls it UNKNOWN, I used the same word.

                                It's helpful, especially in pure SQL, to understand the distinction between expressions and conditions.  Expressions can be NULL, while conditions can be UNKNOWN.