borinquenkid commented on code in PR #16583:
URL: https://github.com/apache/grails-core/pull/16583#discussion_r4420306667
##########
grails-doc/src/en/ref/Constraints/unique.adoc:
##########
@@ -64,6 +64,83 @@ username(unique: ['group', 'department'])
In this example `username` must be unique in `group` and `department`. There
might be same logins in different groups or different departments.
+==== Null values in a composite key
+
+A property of the key can be `null`: a `country` that is optional in `language
unique: 'country'`, a `parent` that is `null` for the top level nodes of a
tree, or the constrained property itself when it is `nullable`. Whether such a
row can be a duplicate of another is a question databases answer in two ways
for their unique index, so the constraint has four readings of a `null`,
selected per constraint or for the whole application:
+
+[cols="1,4"]
+|===
+|Reading |A `null` in the key
+
+|`database` (default)
+|Is read the way the unique index of the database does: `skip` where the index
treats `null` values as distinct, `value` where it compares them. The
validation and the index then agree.
+
+|`skip`
+|Is never a duplicate, as in a SQL unique predicate: `('en', null)` twice is
valid, and the lookup is not made.
+
+|`value`
+|Is a value that matches only another `null`: `('en', null)` conflicts with
another `('en', null)` and with no row that has a country. This holds for the
constrained property as well.
+
+|`wildcard`
+|In the group, leaves its property out of the lookup, so it matches every row:
`('en', null)` conflicts with `('en', 'US')`. A `null` constrained property is
not checked.
+|===
+
+With the rows `('en', 'US')`, `('en', null)`, `(null, 'US')` and `(null,
null)`, a new `('en', null)` is a duplicate under `value` and `wildcard` and is
valid under `skip`; a new `(null, 'US')` is a duplicate only under `value`,
where it matches the stored `(null, 'US')`.
+
+The unique index Hibernate creates for the group treats `null` values as
follows, which is what `database` reads. The two Hibernate versions render the
index differently for SQL Server and Db2, so the table says which version each
row describes:
+
+[cols="2,1,1,3"]
+|===
+|Database |Hibernate |Reading |Why
+
+|H2, PostgreSQL, MySQL, MariaDB
+|5 and 7
+|`skip`
+|`null` values are distinct in a unique index.
+
+|SQL Server
+|7
+|`skip`
+|Hibernate 7 creates the index over nullable columns as a filtered unique
index that leaves the rows with a `null` out.
+
+|Db2 for IBM i, Db2 for z/OS
+|7
+|`skip`
+|Hibernate 7 creates the index as `create unique where not null index`, under
which `null` values are not equal to each other.
+
+|SQL Server, Sybase
+|5
+|`value`
+|Hibernate 5 has no filtered-index delegate, so these dialects get a plain
unique constraint, which compares `null` values: `('en', null)` twice is a
violation.
+
+|Db2 (all platforms)
+|5
+|`value`
+|Hibernate 5 creates a plain unique index over nullable columns
(`DB2UniqueDelegate`), which compares `null` values.
+
+|Db2 for Linux, UNIX and Windows
+|7
+|`value`
+|Hibernate 7 creates the index with `exclude null keys`, which leaves out only
a key that is `null` in every column; a key with a `null` in some columns is
indexed and compared.
+
+|Oracle
+|5 and 7
+|`value`
+|A row with a `null` in some of the key columns is indexed, and its other
columns are compared, so `('en', null)` twice is a violation.
+|===
+
+A datastore that keeps no such index, such as MongoDB, reads `database` as
`value`.
Review Comment:
Added the entirely-null key rule next to the table in `unique.adoc`: under
`database`, an entirely null key is accepted everywhere except SQL Server,
Sybase, and Db2 on Hibernate 5; MongoDB, Neo4j, and in-memory datastores accept
an entirely null key and read `value` for partly null keys; an explicit `value`
rejects an entirely null key on every datastore.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
