1 2 3 4 5 Previous Next 60 Replies Latest reply: Dec 20, 2012 8:22 AM by jeneesh Go to original post RSS
      • 30. Re: null values unique key
        cd_2
        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.

        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.
        All I read there is a definition for unique constraints on non-null column values, which Oracle, to my knowledge, adheres. Is there an official definition how NULL values have to be treated? Otherwise all you can argue is that you don't like the implementation for NULLs the way Oracle did, which is ignoring them.

        C.
        • 31. Re: null values unique key
          547137
          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 corresponding column in the other row
          according to Subclause 8.2, "<comparison predicate>", then the result
          of the <unique predicate> is true; otherwise, the result of
          the <unique predicate> is false.
          All I read there is a definition for unique
          constraints on non-null column values, which Oracle,
          to my knowledge, adheres. Is there an official
          definition how NULL values have to be treated?
          Otherwise all you can argue is that you don't like
          the implementation for NULLs the way Oracle did,
          which is ignoring them.
          Please read it again. Apologies for reposting from an earler post, but it's relevant (and I can't figure out how to link to another post):

          When is the predicate false (and hence the constraint violated)? When there are two rows (A and B) in the set T such that

          the value of each column in one row is non-null and is equal to the value of the corresponding column in the other row

          It's pretty clear that this is not the case for (1, NULL) and (1, NULL).
          • 32. Re: null values unique key
            cd_2
            I did. You're assumption is, that the definition includes NULL, but my interpretation is that it's only discussing the equality of non-null columns. I could be wrong of course, and if you can name a product that treats the NULL case according to your interpretation ...

            C.
            • 33. Re: null values unique key
              535692
              If two nulls can never be equal then see this case
              As all we know Distinct is used to supress duplicate values
              Consider the following example.
              SQL> select id from t;

              ID
              ----------
              1
              34
              45


              6
              45
              1

              8 rows selected.

              SQL> select distinct(id) from t;

              ID
              ----------
              1
              6
              34
              45


              5 rows selected.
              Why i am getting single null instead of two nulls...
              I should get two nulls as per ur point of view
              Please clarify it...
              • 34. Re: null values unique key
                John Spencer
                I think you have a semantic problem with what NULL is, at least in Oracle terms. It is not true that NULL <> NULL. NULLs cannot be compared at all, there is nothing to compare. Oracle stores nothing at all for a NULL column.

                In general, Oracle stores a length byte showing the length of the data in the column, then the column data. If the column that is NULL is the last column in the table, Oracle does not even store a length byte. If the NULL column is in the middle of the table, the length byte is 0 and is immediately followed by the length byte for the next column.

                So, how do you compare two things that are not there?
                SQL> CREATE TABLE t (id NUMBER, descr VARCHAR2(10));

                Table created.

                SQL> ALTER TABLE t ADD CONSTRAINT t_unq UNIQUE(id, descr);

                Table altered.

                SQL> INSERT INTO t VALUES (1, NULL);

                1 row created.

                SQL> COMMIT;

                Commit complete.

                SQL> SELECT DUMP(descr) FROM t;

                DUMP(DESCR)
                -----------
                NULL
                However, storing an explicit NULL (CHR(0) like a C style empty string) does actually store something and can be compared and will violate the unique constraint.
                SQL> INSERT INTO t VALUES (1, CHR(0));

                1 row created.

                SQL> INSERT INTO t VALUES (1, CHR(0));
                INSERT INTO t VALUES (1, CHR(0))
                *
                ERROR at line 1:
                ORA-00001: unique constraint (OPS$ORACLE.T_UNQ) violated

                SQL> COMMIT;

                Commit complete.

                SQL> SELECT id, DUMP(descr) FROM t;

                        ID DUMP(DESCR)
                ---------- --------------
                         1 NULL
                         1 Typ=1 Len=1: 0
                John
                • 35. Re: null values unique key
                  547137
                  I could be wrong of course, and if you can name a
                  product that treats the NULL case according to your
                  interpretation ...
                  Yeah. I believe that my interpretation is standard. Check out this MySQL bug: http://bugs.mysql.com/bug.php?id=8173 -- they use the same interpretation that I'm using, and the change in SQL-2003 implies that any ambiguity you may be perceiving was accidental.
                  • 36. Re: null values unique key
                    547137
                    As all we know Distinct is used to supress duplicate
                    values
                    DISTINCT and UNIQUE are two different birds. Here's the SQL-92 definition of "distinct":

                    h) distinct: Two values are said to be not distinct if either:
                    both are the null value, or they compare equal according to
                    Subclause 8.2, "<comparison predicate>". Otherwise they are
                    distinct. Two rows (or partial rows) are distinct if at least
                    one of their pairs of respective values is distinct. Otherwise
                    they are not distinct. The result of evaluating whether or not
                    two values or two rows are distinct is never unknown.


                    Note that this expressly includes your case.
                    • 37. Re: null values unique key
                      William Robertson
                      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 corresponding column in the other row ... then the result of the <unique predicate> is true; otherwise, the result of the <unique predicate> is false.

                      Even if the ANSI definition isn't ambiguous, it's sure as hell confusing.

                      Sometimes I can read it as "Uniqueness is present unless violated by the presence of duplicates in which all values are not null". In this case (1,NULL)(1,NULL) does not count as a violation because it contains nulls, and therefore it must qualify as unique. In the example of (1,NULL)(1,NULL), is the value of each column non-null and equal to the corresponding value in the other row? No, because there are nulls. So we shouldn't have a uniqueness violation, and dkarp has a point.

                      Other times I read it as "Uniqueness is present unless violated by the presence of non-null duplicates", in which case our (1,NULL)(1,NULL) example does contain non-null duplicates and therefore is not unique, and Oracle's implementation of unique indexes and constraints, DISTINCT, GROUP BY, UNION/MINUS/INTERSECT etc complies with the standard.

                      I think I get different results depending on exactly where I lose track of the implicit bracketing and double negatives in the original definition (TRUE UNLESS NOT(X AND NOT Y)).

                      IMO Oracle has chosen the more practical approach. No, there is no setting that will make unique indexes, constraints, DISTINCT, GROUP BY, UNION/MINUS/INTERSECT etc behave differently. Thank goodness.
                      • 38. Re: null values unique key
                        547137
                        I think you have a semantic problem with what NULL
                        is, at least in Oracle terms. It is not true that
                        NULL <> NULL. NULLs cannot be compared at all, there
                        is nothing to compare. Oracle stores nothing at all
                        for a NULL column.
                        [The rest is elided -- it's actually very interesting, but not germane.]

                        I understand what you're saying, and I have absolutely no problem with Oracle raising a constraint violation for (1, CHR(0)) and (1, CHR(0)). That makes sense, and it follows the specifications correctly.

                        But semantically, NULL <> NULL from a SQL perspective (even if they're the same in a storage perspective).
                        • 39. Re: null values unique key
                          547137
                          I could be wrong of course, and if you can name a
                          product that treats the NULL case according to
                          your interpretation ...
                          Yeah. I believe that my interpretation is standard.
                          Check out this MySQL bug:
                          http://bugs.mysql.com/bug.php?id=8173 -- they use
                          the same interpretation that I'm using, and the
                          change in SQL-2003 implies that any ambiguity you
                          may be perceiving was accidental.
                          Postgres does likewise: http://www.postgresql.org/docs/8.1/interactive/indexes-unique.html

                          When an index is declared unique, multiple table rows with equal indexed values will not be allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all of the indexed columns are equal in two rows.
                          • 40. Re: null values unique key
                            cd_2
                            Yeah. I believe that my interpretation is standard.
                            Check out this MySQL bug:
                            http://bugs.mysql.com/bug.php?id=8173 -- they use
                            the same interpretation that I'm using, and the
                            change in SQL-2003 implies that any ambiguity you
                            may be perceiving was accidental.
                            My interpretation of posted rule in pseudo-code:
                            Constraint_Error := TRUE;
                            
                            FOR current_col IN first_col_in_unique_con TO last_col_in_unique_con
                            LOOP
                              IF rowA.Col[current_col] IS NOT NULL AND
                                 rowB.Col[current_col] IS NOT NULL THEN
                                 IF rowA.Col[current_col] <> rowB.Col[current_col] THEN
                                    Constraint_Error := FALSE;
                                 END IF;
                              END IF;
                            END LOOP;
                            
                            IF Constraint_Error THEN
                               RAISE error_message;
                            END IF;
                            That explains both the (1, NULL) and the (NULL, NULL) example. You and ANSI-SQL2K3 (?) are saying, that in fact the code should look like this:
                            Constraint_Error := TRUE;
                            
                            FOR current_col IN first_col_in_unique_con TO last_col_in_unique_con
                            LOOP
                              IF rowA.Col[current_col] IS NOT NULL AND
                                 rowB.Col[current_col] IS NOT NULL THEN
                                 IF rowA.Col[current_col] <> rowB.Col[current_col] THEN
                                    Constraint_Error := FALSE;
                                 END IF;
                              END IF;
                              
                              IF rowA.Col[current_col] IS NULL OR
                                 rowB.Col[current_col] IS NULL THEN
                                 Constraint_Error := FALSE;
                              END IF;
                            END LOOP;
                            
                            IF Constraint_Error THEN
                               RAISE error_message;
                            END IF;
                            I'm not sure if this is an improvement, but that's just me.

                            C.
                            • 41. Re: null values unique key
                              547137
                              My interpretation of posted rule in pseudo-code:
                              Constraint_Error := TRUE;
                              
                              FOR current_col IN first_col_in_unique_con TO last_col_in_unique_con
                              LOOP
                                IF rowA.Col[current_col] IS NOT NULL AND
                                   rowB.Col[current_col] IS NOT NULL THEN
                                   IF rowA.Col[current_col] <> rowB.Col[current_col] THEN
                                     Constraint_Error := FALSE;
                                   END IF;
                                 END IF;
                              END LOOP;
                              
                              IF Constraint_Error THEN
                                 RAISE error_message;
                              END IF;
                              I think you've got your logic slightly wrong there. You need to initialize "Constraint_Error" to FALSE and then set it to TRUE during the loop. And the resulting pseudocode matches my interpretation, too.
                              • 42. Re: null values unique key
                                547137
                                I could be wrong of course, and if you can name a
                                product that treats the NULL case according to
                                your interpretation ...
                                Yeah. I believe that my interpretation is standard.
                                Check out this MySQL bug:
                                http://bugs.mysql.com/bug.php?id=8173 -- they use
                                the same interpretation that I'm using, and the
                                change in SQL-2003 implies that any ambiguity you
                                may be perceiving was accidental.
                                Postgres does likewise:
                                http://www.postgresql.org/docs/8.1/interactive/indexes-unique.html
                                DB2 is, well, it's DB2. They only allow a single NULL in a UNIQUE INDEX, even a single-column UNIQUE index. Oracle doesn't do this -- you can insert as many NULLs as you'd like in a single-column Oracle UNIQUE INDEX.

                                But they seem to know that it's wrong, so they added alternative syntax to support the SQL-92 specification: UNIQUE WHERE NOT NULL. See http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/db2/rbafzmstxcindx.htm for details.
                                • 43. Re: null values unique key
                                  cd_2
                                  I think you've got your logic slightly wrong there.
                                  You need to initialize "Constraint_Error" to FALSE
                                  and then set it to TRUE during the loop. And the
                                  resulting pseudocode matches my interpretation, too.
                                  You're sure? If I'd change it the way you're suggesting, I'd get a constraint error, when the two rows are in fact not equal.

                                  C.
                                  • 44. Re: null values unique key
                                    547137
                                    You're sure? If I'd change it the way you're
                                    suggesting, I'd get a constraint error, when the two
                                    rows are in fact not equal.
                                    Whoops. Think-o. Sorry about that.