Bug #66512 Unique index allows duplicate records containing nulls
| Submitted: | 23 Aug 2012 9:58 | Modified: | 23 Aug 2012 10:45 |
|---|---|---|---|
| Reporter: | Matt Kusnierz | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.1.48-community-log | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | duplicate, INDEX, null, unique | ||
[23 Aug 2012 9:58]
Matt Kusnierz
[23 Aug 2012 10:39]
Valeriy Kravchuk
This is explicitly documented, moreover, it works this way in other RDBMSes I know (see http://en.wikipedia.org/wiki/Null_%28SQL%29#Effect_on_index_operation). So, this is not a bug.
[23 Aug 2012 10:45]
Matt Kusnierz
The fact that the behaviour is inconsistent between the different mysql table storage engines for me makes this a bug. It means that you need to have detailed knowledge of how the table was created in order to know how the index will function and because a DBA could change the underlying storage engine, it means that code cannot relay on consistent functional behaviour when it comes to unique index constaints.
Oracle databases for example do consider nulls equal when enforcing unique index constraints. As does MS SQL server (at least according to the wikipedia page you linked to:
For example, Microsoft SQL Server documentation states the following:[14]
For indexing purposes, NULL values compare as equal. Therefore, a unique index, or UNIQUE constraint, cannot be created if the key values are NULL in more than one row.)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.043 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.