Unique Key violation detected by database ((conn=224764) Duplicate entry 'inactive@testuser1.com'

[DB1]

Hi,

I am trying to do something from Portal custom widget to insert a user record.

So, this is how the current logic is:

var grgetUser = new GlideRecord("sys_user");
			grgetUser.addQuery("email",input.nonUser.nonPUseremail);
			grgetUser.addQuery("active","true");			
			grgetUser.query();
			if (grgetUser.next()) {
// it will update the record
}
else
{
grgetUser.initialize(); 
				grgetUser.first_name = input.nonUser.nonPUserfname; 
				grgetUser.last_name = input.nonUser.nonPUserlname; 
				grgetUser.email = input.nonUser.nonPUseremail; 
				grgetUser.u_function = input.nonUser.nonPUsertitle; 
				grgetUser.u_primary_contact_phone = input.nonUser.nonPusrphone; 
				grgetUser.time_zone = input.nonUser.nonPUsertimezone; 
				grgetUser.user_name = input.nonUser.nonPUseremail; 
				grgetUser.insert();
}

so this else part means that there is no user record found or it could be false with existing user record which is inactive.

So when I try to add an email which is inactive and submit expected behaviour-  it has to create a new record with the above inputs and user name(it has to create a new user record in addition to the same existing email record which is inactive)

But what happens now is it finds the inactive record with the input email that already exist in the system and updates it instead with the below error:

Unique Key violation detected by database ((conn=224764) Duplicate entry 'inactive@testuser1.com' for key 'user_name')

There is a data base index on email and user name field also an on before business rule that trims the user name. I am not sure what has to be updated in order to fix the issue.

Need help on the same.

TIA,

@Dr Atul G- LNG @James Chun @Maddysunil @Sujatha V M @Ankur Bawiskar 

[DB1]

This is what the trim business rule does:

(function executeRule(current, previous /*null when async*/) {

if (gs.getProperty("glide.db.trim_login_username", 'true') == 'true')

current.user_name = trim(current.user_name);

})(current, previous);

[James Chun]

Hi @DB1,

Looking at the error log, looks like you are creating another user with a same user_name which should be unique.

Note that it doesn't matter if the user is active or not, the user_name field must be unique.

Another thing to add, you should query via the user_name field instead of the email address. 

i.e.

			grgetUser.addQuery("user_name",input.nonUser.nonPUseremail);

Cheers

[Sohail Khilji]

Hi @DB1 ,

https://docs.servicenow.com/bundle/vancouver-integrate-applications/page/administer/import-sets/conc...

Seems you are trying to create a new record with the same value for a field that is defined as a 'unique index'. You probably want to update the existing record and not create a new record.