On Thursday, 29 March 2018 08:11:46 UTC+8, Wes Clark wrote: > > IDX_COL1 IDX_COL_2 > null value1 > null value1 > > This does not violate a uniqueness constraint in H2. NULL is considered a > unique value. Does this match the semantics of any other DBMS? > It's a feature of SQL, null values in SQL are not equal to anything including each other. MySQL and PostgreSQL use the same logic for multi-column unique constraints with nulllable columns as H2.
SQL:2003 determines unique constraint as follows: A unique constraint specifies one or more columns of the table as unique > columns. A unique constraint is satisfied > if and only if no two rows in a table have the same non-null values in the > unique columns. You have null values in this example, so these rows do not violate the constraint according to SQL standard. Similar unique predicate (that is not directly related) have a better definition: 2) If there are no two rows in T such that the value of each column in one > row is non-null and is not distinct > from 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. Oracle is quite different, it allows duplicate rows with the null values in all unique columns, but forbids duplicate rows with null values in some of unique columns. This is described in its documentation. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
