Customer was able to create multiple records with the same 'User ID'.
There is no unique index on sys_user.user_name
If the issue is identified in production, it is recommended to take clone of the production to sub-prod and follow the steps in sub-prod first
- Identify and remove all the duplicate records from the sys_user table
- Check the count on the sys_user table
- Create Unique index on the sys_user table on the user-id field. Below is the procedure
- Navigate to System Definition > Tables.
- In the list, find the table you want and click its label.
- Navigate to the Database Indexes related list.
- Click New.
- Use the slushbucket to select the fields you want included in the index.
The order in which you select the fields affects how the index works. If you do not have expertise in database design, you should consult someone who does.
- To create a unique index, check the Unique Index box.
- Click Create Index
- If there are any duplicate records exists, system may not create Unique Index on the table.
- Check the count on the sys_user table.
After creating the unique index, try to create multiple records with the same User Id, following error will occur.
"Unique Key violation detected by database (Duplicate entry '1234' for key 'user_name')"
This means, Unique index creation on User Id is successful.