1 2 3 Previous Next 60 Replies Latest reply: Dec 20, 2012 8:22 AM by jeneesh RSS

    null values unique key

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

          regds
          Ashish
          • 2. Re: null values unique key
            32685
            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
              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
                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
                  ---
                  • 6. Re: null values unique key
                    547137
                    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
                      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
                        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
                          JustinCave
                          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
                            Sorry Justin!I just missed out that!(About Eric's composite index example)
                            • 11. Re: null values unique key
                              547137
                              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
                                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
                                  > 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
                                    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 Previous Next