Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
In MariaDB, unique indexes permit duplicate NULLs.
CREATE TABLE t2 (
|
a int,
|
b int,
|
c int,
|
UNIQUE (a, b, c)
|
);
|
|
|
INSERT INTO t1 VALUES (1, NULL, NULL);
|
INSERT INTO t1 VALUES (1, NULL, NULL);
|
|
|
SELECT * FROM t1;
|
+------+------+------+
|
| a | b | c |
|
+------+------+------+
|
| 1 | NULL | NULL |
|
| 1 | NULL | NULL |
|
+------+------+------+
|
Previously, the SQL standard has not been clear about the behaviour, leaving it to be implemented in multiple ways. For example, SQL Server does not permit the second row to be inserted, while PostgreSQL does.
Now, with the SQL 2023 standard, the behaviour is made explicit:
CREATE TABLE t2 (
|
a int,
|
b int,
|
c int,
|
UNIQUE NULLS DISTINCT (a, b, c)
|
);
|
allows the duplicate NULL to be inserted, while
CREATE TABLE t3 (
|
a int,
|
b int,
|
c int,
|
UNIQUE NULLS NOT DISTINCT (a, b, c)
|
);
|
does not.
Attachments
Issue Links
- relates to
-
MDEV-21527 NULL values allowing duplicate keys in unique index
-
- Closed
-