Unique field when there are multiple NULL records
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-23-2015 06:35 AM
In MySQL and according to database theory, NULL represents an unknown value and therefore multiple rows with null in a 'unique' column cannot violate the uniqueness constraint. There are databases which work differently (e.g., MS SQLServer) but the ANSI/ISO SQL standard behaviour is what MySQL does. More information at NULL and UNIQUE | SQL And Its Sequels
My question at the end of this post relates to a problem I am currently experiencing. I have two columns in a table, say table T, in ServiceNow, let's call them U and V. U is already set to Unique in the Data Dictionary. There are 1000s of records in the table of which 25 have U=null. When I try to add another record with U=null, it's quite happy. If I try to add a record with U the same as another record, there is an error - perfectly acceptable and correct.
However, when I try to make V unique as well, where V has 110 records with V=null, I get an error that there are non-unique values in column V and I should remove them and try again. I have checked again and again that the only repeated value in V is null. So I strongly believe that I should be able to make V unique as it will be the same situation as U.
Is this a bug in ServiceNow? I can only guess that U was made unique before the NULL records were added, but it is definitely inconsistent.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-24-2015 04:34 AM
I checked that the values are really null using a background script.
Both U and V are String type, one of length 40 and the other of length 100.
After further tests yesterday I'm now certain that this is at best an inconsistency and at worst a bug. I changed the nulls to unique values, enforced uniqueness, then could add multiple records with null in field V again. The inconsistency/bug is that it refuses to allow the addition of the uniqueness constraint when multiple null records are present.
I also found that, contrary to the warning in the documentation that "All duplicate records are deleted" (http://wiki.servicenow.com/index.php?title=Creating_New_Fields#Requiring_Unique_Values_for_a_Field), adding uniqueness does not delete duplicate records. Instead, there is an error message that the constraint cannot be added.
