An alter table database error can occur when upgrading from CMS6 to CMS7.

I recently tried to update a customers website from CMS6 R2 to CMS7 and got an error. The error is a database error and the reason is that my root page id <> 1. This customer had root id = 5.

This is the error message:

An unhandled error has occured:
The ALTER TABLE statement conflicted with the FOREIGN KEY SAME TABLE constraint
 "FK_tblContent_tblContent". The conflict occurred in database "dbForskningspar
kenUpgrade", table "dbo.tblContent", column 'pkID'.
When executing
At C:\Program Files (x86)\EPiServer\CMS\7.0.586.1\Upgrade\System Scripts\Upgrad
e Site Database (SqlServer).ps1:9 char:25
+       Execute-EPiSqlSvrScript <<<<  -EPiServerScript -ScriptPath $file 
-SqlServerName $dbConnection.DataSource -DatabaseName $dbConnection.InitialCatalog 
-LoginName $dbConnection.UserID -LoginPassword $dbConnection.Password

A god thing, it’s easy to do a workaround:

* Open  this script :
"C:\Program Files\EPiServer\CMS\7.0.586.1Upgrade\Database\sql\ 0007_0000_0147_0000to0007_0000_0361_0000.sql".

* Find this insert:

INSERT INTO tblContent (fkContentTypeID,fkParentID,ArchiveContentGUID, CreatorName,
ContentGUID,VisibleInMenu,Deleted,PendingPublish,ChildOrderRule,PeerOrder,
ExternalFolderID,fkMasterLanguageBranchID,ContentPath,ContentType) VALUES (
@fkContentTypeId,--fkContentTypeID
1,--fkParentID

* Change the 1 to your root page id, in my case 5.

* Rerun the update from CMS6 to CMS7 in Deployment Center. Worked like a charm, except for the compilation error, but that’s another story.