One post tagged with "list creation"

List Creation Fails Due to list_permissions Constraint Conflict​

In Control Room versions prior to 4.2.108, list creation may fail due to conflicting foreign key constraints on the list_permissions table. Specifically, the presence of two additional constraints fk_list_permissions_lists_list_id and fk_list_permissions_users_user_id can cause constraint violations during list creation.

This issue was addressed in version 4.2.108 by retaining only the essential primary key constraint, thereby resolving the conflict.

If your system is running any version older than 4.2.108.0 and you cannot upgrade immediately, a manual workaround is available. You can remove the conflicting constraints using pgAdmin (for PostgreSQL) or SQL Server Management Studio (for MSSQL). However, upgrading to version 4.2.108.0 or later is strongly recommended to avoid encountering this issue in the future.

Applies To​

This issue affects Control Room version 4.2.107, specifically when using PostgreSQL or Microsoft SQL Server as the database.

Symptoms​

• Unable to create a new list from the Control Room.
• Application logs show constraint violations or foreign key errors related to the list_permissions table.
• List creation consistently fails when saving the new list.

Root Cause​

In CR version 4.2.107, the list_permissions table includes the following constraints:

• pk_list_permissions_list_perm_id (Primary Key)
• fk_list_permissions_lists_list_id (Foreign Key)
• fk_list_permissions_users_user_id (Foreign Key)

These foreign key constraints conflict with list creation operations, resulting in violations. In version 4.2.108, the schema was updated to retain only the primary key constraint, which resolves this issue.

Resolution (Workaround for 4.2.107)​

Manual Resolution for Version 4.2.107, follow the steps below based on your database:

For PostgreSQL​

1. Open pgAdmin.

2. Navigate to the active database.

3. Expand Schemas → public → Tables → list_permissions.

4. Expand the Constraints section.

5. Delete the following foreign key constraints:

   • fk_list_permissions_lists_list_id
   • fk_list_permissions_users_user_id
     (Right-click each constraint → Click Delete)

6. Ensure that the primary key pk_list_permissions_list_perm_id still exists.

7. If it does, rename it to pk_list_permissions:
   (Right-click → Properties → Change Name → Click Save)

   note

   This renaming ensures consistency with the updated schema in v4.2.108

For Microsoft SQL Server (MSSQL)​

1. Open SQL Server Management Studio (SSMS).

2. Expand the Databases section.

3. Navigate to the active database.

4. Go to Tables → ListPermissions.

5. Expand the Keys section.

6. Delete the following foreign key constraints:

   • fk_list_permissions_lists_list_id
   • fk_list_permissions_users_user_id
     (Right-click each constraint → Click Delete)

7. Ensure that the primary key pk_list_permissions_list_perm_id still exists.

8. If it does, rename it to PK_ListPermissions:
   (Right-click → Rename → Enter PK_ListPermissions)

   note

   This renaming ensures consistency with the updated schema in v4.2.108

Recommendation​

To permanently resolve this issue, upgrade to Control Room version 4.2.108 or later, where the conflicting constraints have been removed.