Developer forum

Forum » Integration » Data Integration Task Violates Primary Key Set on AccessUserTable

Data Integration Task Violates Primary Key Set on AccessUserTable

E-mail notifications
Carolee Schuck Dynamicweb Employee
Carolee Schuck
Reply
Posted on 13/12/2022 19:30:37

Hi,

I found on a 9.10.13 site (https://dev-syndicate.mydwsite1.com/Admin/Default) that the User Provider allows the import of duplicate values to the primary key field.

Duplicates are allowed only the first time the record is created in Dynamicweb:

  • If the first batch contains 40 duplicate records with an AccessUserExternalId that does not already exist in the databae, 40 records will be created
  • Once the record exists, we see the expected behavior of updating just the one existing record

To repro:

  1. Query: delete from accessuser where accessuseremail like '%sutton@syndi%' and AccessUser_UserType = 'Login'
  2. Save the XML in the attached file to Files\Files\Integration\ImportLogins.xml
  3. Run the _FSCMImport Logins Data Integration job
  4. Query: select accessuserid, accessuserusername, accessuseremail, AccessUserGroups, accessuserexternalid, AccessUserCreatedOn, AccessUser_LastSyncDate from accessuser where accessuseremail like '%sutton@syndi%' and AccessUser_UserType = 'Login'
  5. Note that all 4 records were created at the same time

Expected behavior: the import should fail on the insertion of duplicate values to the selected primary key column

Note: setting the “Discard duplicate key rows” Destination Settings parameter behaves correctly in preventing duplicates.

Thanks for your help,
Carolee


Replies

 
Dmitriy Benyuk Dynamicweb Employee
Dmitriy Benyuk
Posted on 14/12/2022 08:39:33
Reply
This post has been marked as an answer

Hi Carolee,
that is the problem because the selected "AccessUserExternalId" column as a Key is not a real "PK" column in the AccessUser table so the users just pass and are inserted to db.
But you can check the specific table mapping option to "Discard duplicate key rows" so it will be applied to only that one table mapping but not to the whole job.
It can be set like that:

BR, Dmitrij

Votes for this answer: 1
 
Carolee Schuck Dynamicweb Employee
Carolee Schuck
Posted on 14/12/2022 17:45:55
Reply

Thanks, Dmitrij.

Maybe I misunderstand the use of the primary key setting.  Can you let me know its purpose, please?

 
Dmitriy Benyuk Dynamicweb Employee
Dmitriy Benyuk
Posted on 14/12/2022 18:26:52
Reply

It is described here. The problem is that for the Insert when the real Int AccessUserId is not in the job then it auto creates the users with autoids created.
Ideally it should not allow the situation like that but that is not implemented so you should use a workaround with Discard duplicates for now 

 

You must be logged in to post in the forum