Unique field when there are multiple NULL records

[johnsgp]

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.

[Michael Fry1]

Before you enforce uniqueness on a field, verify that no records in the table for the field have values, or that they all have the same value.   wiki: http://wiki.servicenow.com/index.php?title=System_Dictionary#gsc.tab=0

So I think all the values need to be the same (null) before you enable Unique.

[johnsgp]

I have read that warning over and over again and it makes no sense to me.   You can only enforce null when they are all different, and then there is no data loss.   When they are all the same, whether null or not, ServiceNow refuses to allow you to change Unique to true.

I am sure it is a mistake in the documentation.   I would have written:

"Before you enforce uniqueness on a field, verify that no two records in the table for the field have the same value.   Attempting to make a field unique when the corresponding table has the same value in more than one record results in an error message."

[sivrajb]

Hi - But does that mean that if I set the column to Null, and enabled the Unique, will multiple Null values be allowed in the same column?

What I wasn't clear of is - Does ServiceNow look at multiple NULL field values in a column as duplicates, or it allows it?

[bernyalvarado]

Hi, some thoughts:

a) Would it be possible that for V, your null values are actually strings and not empty (system NULL). You could perhaps create a background script to validate this by using JSUtil.notNil('field_name') which will validate if the field is NULL (system NULL) or blank.

b) Are the data type of U and V the same? If not, you may want to check into that direction.

Thanks,

Berny