This discussion is archived
12 Replies Latest reply: Sep 10, 2013 7:04 AM by Frank Kulash RSS

Unique Constraint

940315 Newbie
Currently Being Moderated

can unique accept multiple null value in column or not ?

  • 1. Re: Unique Constraint
    Hoek Guru
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    Yes

  • 5. Re: Unique Constraint
    940315 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    > 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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    > 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 Expert
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points