Foreign Key Conflict on GroupId During Upgrade (tbl_pj_GroupRightAssociation) – Resolution for Legacy Projects

Symptoms

During Database Upgrade (or when running SP_UPGRADE_PROJECT_DATABASE_V2) you receive:

Error occurred while upgrading the project database.
The INSERT statement conflicted with the FOREIGN KEY constraint "fk_tbl_pj_GroupRightAssociation_GroupId_tbl_pj_GroupInfo".
The conflict occurred in database "prj_XXXXXX", table "dbo.tbl_pj_GroupInfo", column 'GroupId'.

Followed by hundreds of harmless warnings:

Warning: Null value is eliminated by an aggregate or other SET operation.

Cause

Very old Venio installations (pre-2018) often contain duplicate/orphaned security groups that were created automatically over the years. These groups:

• Have no users assigned
• Still exist in rights/association tables
• Violate the stricter foreign-key constraints introduced in later versions

Resolution

1. Run the official cleanup script below on the affected project database (e.g., prj_WILE0001_RR_Acme).
2. After the script completes successfully, re-run the project upgrade:

exec SP_UPGRADE_PROJECT_DATABASE_V2 <ProjectID>

Official Cleanup Script (Issue in groupid when upgrading.sql)

IF OBJECT_ID('tbl_pj_GroupInfo_bak_101800') IS NULL
    SELECT * INTO tbl_pj_GroupInfo_bak_101800 FROM tbl_pj_GroupInfo

DECLARE @GroupIdTable TABLE (GroupId BIGINT)

INSERT INTO @GroupIdTable
SELECT GroupId
FROM (
    SELECT ROW_NUMBER() OVER(
        PARTITION BY SUBSTRING(G.GROUPNAME, LEN(G.GROUPNAME)-CHARINDEX('-',REVERSE(G.GroupName))+1, LEN(G.GROUPNAME)) 
        ORDER BY U.groupid desc, G.GROUPID DESC) AS RN, 
        G.GroupId , 
        u.GroupId as UserGroupId
    FROM tbl_pj_GroupInfo G  
    LEFT JOIN (SELECT DISTINCT GROUPID FROM tbl_pj_GroupUserAssociation) U  
        ON G.GROUPID = U.GroupId 
) S
WHERE RN  1 AND UserGroupId IS NULL

BEGIN TRAN

DELETE g FROM [dbo].[tbl_fld_GroupFieldComponentAssociation] g INNER JOIN @GroupIdTable t ON g.UserGroupId = t.groupid
DELETE g FROM [dbo].[tbl_fld_GroupFieldAssociation] g INNER JOIN @GroupIdTable t ON g.UserGroupId = t.groupid
DELETE g FROM [dbo].[tbl_tg_TagProjectGroupAssociation] g INNER JOIN @GroupIdTable t ON g.GroupId = t.groupid
DELETE g FROM [dbo].[tbl_pj_GroupRightAssociation] g INNER JOIN @GroupIdTable t ON g.GroupId = t.groupid
DELETE g FROM tbl_pj_GroupInfo g INNER JOIN @GroupIdTable t ON g.GroupId = t.groupid

DECLARE @ProjectName nvarchar(MAX)
SELECT @ProjectName = ProjectName 
FROM tbl_pj_ProjectSetup WITH(NOLOCK) 
WHERE DatabaseInstanceName = DB_NAME()

UPDATE tbl_pj_GroupInfo 
SET GroupName = @ProjectName + SUBSTRING(GROUPNAME, 
    LEN(GROUPNAME)-CHARINDEX('-',REVERSE(GroupName))+1, LEN(GROUPNAME))

COMMIT

Is the script safe?

Yes. It has been used by Venio Support on hundreds of legacy upgrades. It only removes duplicate groups that:

• Have no users assigned
• Are not the most recent version of that logical group

A backup of tbl_pj_GroupInfo is automatically created as tbl_pj_GroupInfo_bak_101800 the first time the script runs.

Prevention / Best Practice

• Keep this script in your Venio toolkit. You will need it for every remaining legacy project on old servers.
• After upgrading to 11.8+, this issue no longer occurs because the group creation logic was fixed years ago.

References

• Venio Support script version used since ~2021

Venio Systems – Global Support Knowledge Base