Setting the Serial Number field of the CMDB or Asset tables may seem a good idea for preventing Duplicate CIs and Assets, but it really isn't. These are some of the reasons why this is not unique by design, and should stay that way:
- Serial Numbers are not unique. There is no central world organisation like ISO for issuing blocks of serial numbers. There is for network MAC addresses and public IP addresses, making those good for unique identification, but there isn't for Serial Numbers.
- Several components within the same system may share the same serial number, or at least report the same serial number when queried via the management interface. For example Stacked Switches, Storage enclosures, Blade Chassis, cloned Virtual Machines and others.
- Setting a Unique constraint in the Dictionary applies at the SQL Database level, not the form or application level. This means an insert or update attempting to set a serial number that already exists in another record will cause a database Exception, crashing the transaction at that point, with no chance of handling the error in a safe way. The error you will see in the system log will look a lot like:
FAILED TRYING TO EXECUTE ON CONNECTION xx: INSERT INTO cmdb (,...,`serial_number`,...) VALUES(...,'123456',...),INSERT INTO cmdb$par1 (...) VALUES(...)
java.sql.BatchUpdateException: Duplicate entry '123456' for key 'serial_number'
- If duplicate values already exist when Unique is first set, you will get Data Loss. At the point that the Unique checkbox is set, the SQL database builds a unique index for the field. Any records that don't fit that new constraint are deleted so that the SQL data is consistent with the column/index settings. If the unique field is only defined on one partition of the CMDB, then only part of the record will be deleted, leaving an orphan sys_id in the other table partition, corrupting the whole CMDB.
- The Serial Number field for the main CI is not the primary field for identification, because a CI can have several different Serial Numbers, in the same way that the device may have several IP addresses. We use the cmdb_serial_number table for storing all those different serial numbers by type, and often they are the same. For example the System Serial Number may be the same as the BIOS Serial Number. Where 2 CIs correctly have the same serial number in the main CIs Serial Number field, we don't generally have a problem because the separate Serial Number table records for the devices will contain different ones for unique identification.
- Out-of-box code expects to be able to create CIs with serial numbers the same as other CIs. That is the design of the features, and setting serial number as unique will break that functionality. For example Discovery Sensor code may continue running thinking it has successfully inserted a record, and use the sys_id it thought it had created in reference fields in other records that do get created, breaking things like relationships with other CIs and Service Maps.
- The correct use of the Identification and Reconciliation rules and APIs will accomplish the same goal. During inserts and updates through that API, duplicate CIs are detected and the appropriate action taken automatically. Where existing CIs appear to be the same, based on several identification fields, de-duplication tasks are created, and Duplicate CIs can be reported on in the CMDB Health Dashboard. Preventing duplicates by the correct identification of existing records during inserts and updates is the main purpose of the feature.
Most of these points also apply to other fields in the CMDB, such as as Name, Asset Tag, Correlation ID, etc.
Release or Environment
If you have a company policy or other technical reason why serial numbers in the CMDB or Asset table must be unique, then a less harmful workaround would be to use Abort action Business Rules to accomplish the same thing. These can run on insert or update, and have conditions to apply only to the CI Classes where uniqueness is absolutely required. Your custom business rule logic could also include logging and reporting so that CMDB administrators can try to identify and fix the causes of the 'bad' inserts/updates and avoid it happening.
If the unique constraint is a workaround for Import Sets, then using the Identification Engine in the transform would be the better solution:
Documentation: Apply CI Identification and Reconciliation to Import Sets
API reference: CMDBTransformUtil - Global
If you have already set a CMDB field as Unique and are having problems, then you will need to contact ServiceNow Technical Support, as admin role users cannot delete a unique index once it is created.