Despite the SQL standard, the Oracle semantics hue closer to our business requirements. For example, Col1 could be a field on a subtype reflecting a relational mapping, and we'd like it to be constrained to be unique when it has a value. It is paired up in the index with "version number" field used to "retire" an instance of that type. The version number is usually 0 but can be a positive number to denote a retired instance. The Oracle semantics map to this use case.
On Wed, Mar 28, 2018 at 7:02 PM, Evgenij Ryazanov <[email protected]> wrote: > 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 a topic in the > Google Groups "H2 Database" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/h2-database/flkyvMXSQWE/unsubscribe. > To unsubscribe from this group and all its topics, 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. > -- 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.
