Symptom
The following error might occur during Database Setup:
This error might occur during Database Setup Adding referential integrity... processing.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "UA_USER_FK". The conflict occurred in database 'AccessControl', table'dbo.USERS', column 'USERID'. Details: Database Error executing a statement. (ALTER TABLE DBO.USER_ACCOUNT ADD CONSTRAINT UA_USER_FK FOREIGN KEY (USERID) REFERENCES DBO.USERS (USERID) An error occurred while trying to create foreign key UA_USER_FK.This error might occur during Database Setup Adding referential integrity... processing.
Resolution
Important: Before performing the following steps to resolve the error, back up your database, and verify the integrity of the backup.
- To display the orphan database records causing the error, execute the following query against the ACCESSCONTROL database:
SELECT * FROM USER_ACCOUNT WHERE USERID NOT IN (SELECT USERID FROM USERS) - Delete any records returned by the query in step 1 by executing the following query against the ACCESSCONTROL database:
DELETE FROM USER_ACCOUNT WHERE USERID NOT IN (SELECT USERID FROM USERS) - Run Database Setup.
At this point, Database Setup should run and complete successfully. If it does not, there might be more issues with orphan records. Contact Lenel Technical Support for further assistance.
Applies To
OnGuard (All versions)
Additional Information
In systems running SQL Server, this issue is typically caused by not enabling recursive triggers in the ACCESSCONTROL database. To enable recursive triggers, follow the instructions in the Installing or Upgrading Microsoft SQL Server chapter of the Installation Guide.
