Kentico 12 Upgrade – SettingsCategory Constraint Error

When upgrading from Kentico 11 to 12, some projects encounter constraint errors during the SQL portion of the upgrade, where the script fails to delete the CMS.UrlShortening.Googl settings category.

This seems to happen when there are multiple settings categories with this name. Because the SQL script uses TOP 1 when selecting the category of certain settings keys being deleted, it can happen that that select returns the empty duplicate category instead of the one that actually contains keys. This means the keys never get deleted, causing errors when deleting the category they belong to.

The full error text usually looks something like this:

Failed to run SQLscript: 
[DataConnection.HandleError]: 

Query: 

DECLARE @categoryParentID int;
SET @categoryParentID = (SELECT TOP 1 [CategoryID] FROM [CMS_SettingsCategory] WHERE [CategoryName] = 'CMS.UrlShortening')
IF @categoryParentID IS NOT NULL BEGIN

DECLARE @categoryResourceID int;
SET @categoryResourceID = (SELECT TOP 1 [ResourceID] FROM [CMS_Resource] WHERE [ResourceGUID] = 'aafd78f2-91f7-47cc-bf0b-d1a048d9540a')
IF @categoryResourceID IS NOT NULL BEGIN

DELETE FROM [CMS_SettingsCategory]
    WHERE [CategoryName] = 'CMS.UrlShortening.Googl' AND [CategoryParentID] = @categoryParentID

END

END

Caused exception: 
The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_SettingsKey_KeyCategoryID_CMS_SettingsCategory". The conflict occurred in database "", table "dbo.CMS_SettingsKey", column 'KeyCategoryID'.
The statement has been terminated.

To solve this problem, roll back and delete the duplicate settings category prior to applying the SQL script. You can figure out which of the keys is the duplicate in a couple ways.

I’ve only ever seen this happen in such a way that the duplicate category contained no keys. In this case, you can figure out which one is the duplicate with a query like SELECT * FROM CMS_SettingsCategory WHERE CategoryName = 'CMS.UrlShortening.Googl' AND CategoryID NOT IN (SELECT DISTINCT KeyCategoryID FROM CMS_SettingsKey)

If you have settings keys under both categories, you can figure out which one is the default by the resource it’s associated with. The normal, out-of-box googl category is associated with the CMS.Categories resource. If one of the categories is associated with some other resource, then that category and its keys can be removed. SELECT CategoryName, ResourceName FROM CMS_SettingsCategory JOIN CMS_Resource ON ResourceID = CategoryResourceID WHERE CategoryName = 'CMS.UrlShortening.Googl'

Once you know which category to delete, you can delete the keys (if applicable) and category with something like this:

Declare @id int;
SET @id = <THE ID OF THE CATEGORY YOU WANT TO DELETE>;
DELETE FROM CMS_SettingsKey WHERE KeyCategoryID = @id;
DELETE FROM CMS_SettingsCategory WHERE CategoryID = @id;