This discussion is archived
1 2 3 5 Previous Next 60 Replies Latest reply: Dec 20, 2012 6:22 AM by jeneesh RSS

null values unique key

502435 Newbie
Currently Being Moderated
how many null values r supported by oracle for a unique key
  • 1. Re: null values unique key
    451529 Newbie
    Currently Being Moderated
    no limit...........
    bcoz no null is equivalent to another null.

    regds
    Ashish
  • 2. Re: null values unique key
    32685 Expert
    Currently Being Moderated
    Hello

    While that is true for a unique index on columns where all values are null, it is not the case where one of the values is not null:
    SQL> CREATE TABLE dt_test_nulls (id number, col1 varchar2(1))
      2  /

    Table created.

    SQL> CREATE UNIQUE INDEX dt_test_nulls_i1 on dt_test_nulls(id)
      2  /

    Index created.

    SQL> insert into dt_test_nulls values(null,'Y')
      2  /

    1 row created.

    SQL> insert into dt_test_nulls values(null,'N')
      2  /

    1 row created.

    SQL> create unique index dt_test_nulls_i2 on dt_test_nulls(id,col1)
      2  /

    Index created.

    SQL> insert into dt_test_nulls values(null,'N')
      2  /
    insert into dt_test_nulls values(null,'N')
    *
    ERROR at line 1:
    ORA-00001: unique constraint (BULK1.DT_TEST_NULLS_I2) violated

    SQL> insert into dt_test_nulls values(null,null)
      2  /

    1 row created.

    SQL> insert into dt_test_nulls values(null,null)
      2  /

    1 row created.
    I just thought it was worth pointing out.

    HTH

    David

    Message was edited by:
    david_tyler
  • 3. Re: null values unique key
    497324 Newbie
    Currently Being Moderated
    That is the basic difference btween unique and primary key.

    Uniqu = No duplicate , but allow any number of NULLs
    Primary key = No duplicate and no NULLs
  • 4. Re: null values unique key
    435868 Newbie
    Currently Being Moderated
    The reason for this - at first sight - strange behaviour relies upon the fact that NULL values are not included in indexes ...

    Note :
    - unique key = unique index
    - primary key = unique index + not null constraint

    That's why inserting multiple NULLs into a uniquely indexed column does not raise any error. Now, the reason why an error is raised when the index lies upon two (or more) columns, while not all values are NULL, is obvious : the NOT NULL values are picked up by the unique index, en must consequently be unique...

    Hope this makes things clear...

    Greetz,
    Philip.
  • 5. Re: null values unique key
    496898 Newbie
    Currently Being Moderated
    ---
  • 6. Re: null values unique key
    547137 Newbie
    Currently Being Moderated
    Now, the
    reason why an error is raised when the index lies
    upon two (or more) columns, while not all values are
    NULL, is obvious : the NOT NULL values are picked up
    by the unique index, en must consequently be
    unique...
    So here's a followup question: If you've got a 2-column unique index and the inserted rows are (NULL, 2) and (NULL, 2), do you get an error? Or is it only when NULL isn't the first column in the index?
  • 7. Re: null values unique key
    SomeoneElse Guru
    Currently Being Moderated
    Why not try a quick test...
    SQL> create table test_null
      2  (col1     number
      3  ,col2     number);

    Table created.

    SQL>
    SQL> create unique index test_null_idx on test_null(col1,col2);

    Index created.

    SQL>
    SQL> insert into test_null values (null,2);

    1 row created.

    SQL> insert into test_null values (null,2);
    insert into test_null values (null,2)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (ERICH.TEST_NULL_IDX) violated
  • 8. Re: null values unique key
    545369 Newbie
    Currently Being Moderated
    If we create a UNIQUE KEY ,we can insert any number of null values

    SQL> create table test_tab(t1 number,t2 number);

    Table created.

    SQL> ALTER TABLE test_tab ADD CONSTRAINT uk_t1 unique(t1);

    Table altered.

    SQL> insert into test_tab values(null,1);

    1 row created.

    SQL> insert into test_tab values(null,2);

    1 row created.

    SQL> insert into test_tab values(null,1);

    1 row created.

    It it's a UNIQUE INDEX ,we get unique constraint violated error message
  • 9. Re: null values unique key
    Justin Cave Oracle ACE
    Currently Being Moderated
    There is no difference between a unique constraint and a unique index here (the unique constraint creates a unique index under the covers). The difference between your example and Eric's is that he is using a composite index (an index on both col1 and col2) and you are using an index on just col1.

    Justin
  • 10. Re: null values unique key
    545369 Newbie
    Currently Being Moderated
    Sorry Justin!I just missed out that!(About Eric's composite index example)
  • 11. Re: null values unique key
    547137 Newbie
    Currently Being Moderated
    Why not try a quick test...
    ORA-00001: unique constraint (ERICH.TEST_NULL_IDX) violated
    Thanks! But, since NULL <> NULL, this just seems wrong. Is there any way to configure Oracle so that it behaves in a more standards-compliant way in this respect?
  • 12. Re: null values unique key
    SomeoneElse Guru
    Currently Being Moderated
    this just seems wrong
    No, it's exactly correct.

    A unique index means that each entry will point to one and only one row. If you have

    NULL,2
    NULL,2

    it won't work (thank goodness).
    configure Oracle so that it behaves in a more standards-compliant way
    What "standards" are being violated by Oracle?
  • 13. Re: null values unique key
    William Robertson Oracle ACE
    Currently Being Moderated
    > But, since NULL <> NULL, this just seems wrong.

    Perhaps, but it would also seem wrong if a unique constraint/index allowed both values. When considering nulls, you sometimes need a little Zen duality.
  • 14. Re: null values unique key
    547137 Newbie
    Currently Being Moderated
    configure Oracle so that it behaves in a more standards-compliant way
    What "standards" are being violated by Oracle?
    Well, ANSI SQL-92 section 11.7 defines a UNIQUE constraint as enforcing UNIQUE ( SELECT UCL FROM TN ). And ANSI SQL-92 section 8.9 defines the UNIQUE predicate as

    1) Let T be the result of the <table subquery>.

    2) If there are no two rows in T such that the value of each column
    in one row is non-null and is equal to the value of the cor-
    responding column in the other row according to Subclause 8.2,
    "<comparison predicate>", then the result of the <unique predi-
    cate> is true; otherwise, the result of the <unique predicate>
    is false.


    So it's pretty clear that Oracle's in the wrong here. My question is whether there's a way to throw Oracle into a standards-compliant mode in this respect.
1 2 3 5 Previous Next