I figured that taking the site to 4.11.9 before upgrading would help but no. I've posted what happends in the referenced log, /App_Data/Logs/UmbracoTraceLog.txt, here https://dl.dropboxusercontent.com/u/6208162/upgrade_fail.txt

When having a look at the log we can see that the table 'umbracoServer' was created, when done with that the table 'cmsTab' is renamed to 'cmsPropertyTypeGroup'. This seems fine to me and I can run this manually. The first ALTER TABLE runs just fine, that is

ALTER TABLE [cmsPropertyTypeGroup] ADD [parentGroupId] INTEGER NULL

However the second ALTER TABLE fails miserably

ALTER TABLE [cmsPropertyTypeGroup] ADD CONSTRAINT [FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup] FOREIGN KEY ([parentGroupId]) REFERENCES [cmsPropertyTypeGroup] ([id])

and that is what's generating my error, "Database configuration failed with the following error and stack trace: There are no primary or candidate keys in the referenced table 'cmsPropertyTypeGroup' that match the referencing column list in the foreign key 'FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup'."

Time to figure out what this upgrade actually do!

Another thing to check is if the "cmsPropertyTypeGroup" table actually has a primary key set on the "id" column. If you verified that the "parentGroupId" was added to the "cmsPropertyGroup" table then I can't think of anything else that would cause the addition of that foreign key constraints to fail.

- Morten

@Morten, yeah I figured that and are building a check/fix script for the Umbraco 4.9 database. It seems as if all KEYS and CONSTRAINTS have gone missing somehow. This installation have a few years on its neck and have been migrated between versions since 4.5 beta. 

I'll post the fix-script here if anyone else are having these issues, thanks.

Here is my script that fixed the entire 4.9 database

Execute directly in your SQL Server database

---- cmsContent ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsContent' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsContent] ADD CONSTRAINT [PK_cmsContent] PRIMARY KEY CLUSTERED ([pk] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
    CONSTRAINT [IX_cmsContent] UNIQUE NONCLUSTERED ([nodeId] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 




---- cmsContentType ----
-- SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'cmsContentType' AND TABLE_SCHEMA ='dbo'
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsContentType' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsContentType] ADD CONSTRAINT [PK_cmsContentType] PRIMARY KEY CLUSTERED ([pk] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
    CONSTRAINT [IX_cmsContentType] UNIQUE NONCLUSTERED ([nodeId] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 


---- cmsContentTypeAllowedContentType ----
-- SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'cmsContentTypeAllowedContentType' AND TABLE_SCHEMA ='dbo'
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsContentTypeAllowedContentType' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsContentTypeAllowedContentType] ADD CONSTRAINT [PK_cmsContentTypeAllowedContentType] PRIMARY KEY CLUSTERED ([Id] ASC, [AllowedId] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- cmsContentVersion ----
-- SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'cmsContentVersion' AND TABLE_SCHEMA ='dbo'
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsContentVersion' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsContentVersion] ADD CONSTRAINT [PK__cmsConte__3213E83F3B75D760] PRIMARY KEY CLUSTERED ([id] ASC) 
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
    CONSTRAINT [IX_cmsContentVersion] UNIQUE NONCLUSTERED ([VersionId] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 




---- cmsContentXml ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsContentXml' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsContentXml] ADD CONSTRAINT [PK_cmsContentXml] PRIMARY KEY CLUSTERED ([nodeId] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- cmsDataType ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsDataType' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsDataType] ADD CONSTRAINT [PK_cmsDataType] PRIMARY KEY CLUSTERED ([pk] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
    CONSTRAINT [IX_cmsDataType] UNIQUE NONCLUSTERED ([nodeId] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 




---- cmsDataTypePreValues ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsDataTypePreValues' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsDataTypePreValues] ADD CONSTRAINT [PK_cmsDataTypePreValues] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 




---- cmsDictionary ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsDictionary' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsDictionary] ADD CONSTRAINT [PK_cmsDictionary] PRIMARY KEY CLUSTERED ([pk] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
    CONSTRAINT [IX_cmsDictionary] UNIQUE NONCLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- cmsDocument ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsDocument' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsDocument] ADD CONSTRAINT [PK_cmsDocument] PRIMARY KEY CLUSTERED ([versionId] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
    CONSTRAINT [IX_cmsDocument] UNIQUE NONCLUSTERED ([nodeId] ASC, [versionId] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- cmsDocumentType ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsDocumentType' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsDocumentType] ADD CONSTRAINT [PK_cmsDocumentType] PRIMARY KEY CLUSTERED ([contentTypeNodeId] ASC, [templateNodeId] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 




---- cmsLanguageText ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsLanguageText' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsLanguageText] ADD CONSTRAINT [PK_cmsLanguageText] PRIMARY KEY CLUSTERED ([pk] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- cmsMacro ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsMacro' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsMacro] ADD CONSTRAINT [PK_macro] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- cmsMacroProperty ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsMacroProperty' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsMacroProperty] ADD CONSTRAINT [PK_macroProperty] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 




---- cmsMacroPropertyType ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsMacroPropertyType' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsMacroPropertyType] ADD CONSTRAINT [PK_macroPropertyType] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- cmsMember ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsMember' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsMember] ADD CONSTRAINT [PK_cmsMember] PRIMARY KEY CLUSTERED ([nodeId] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 




---- cmsMember2MemberGroup ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsMember2MemberGroup' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsMember2MemberGroup] ADD CONSTRAINT [PK_cmsMember2MemberGroup] PRIMARY KEY CLUSTERED ([Member] ASC, [MemberGroup] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 




---- cmsMemberType ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsMemberType' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsMemberType] ADD CONSTRAINT [PK_cmsMemberType] PRIMARY KEY CLUSTERED ([pk] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 




---- cmsPreviewXml ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsPreviewXml' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsPreviewXml] ADD CONSTRAINT [PK_cmsContentPreviewXml] PRIMARY KEY CLUSTERED ([nodeId] ASC, [versionId] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- cmsPropertyData ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsPropertyData' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsPropertyData] ADD CONSTRAINT [PK_cmsPropertyData] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 




---- cmsPropertyType ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsPropertyType' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsPropertyType] ADD CONSTRAINT [PK_cmsPropertyType] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- cmsStylesheet ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsStylesheet' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsStylesheet] ADD CONSTRAINT [PK_cmsStylesheet] PRIMARY KEY CLUSTERED ([nodeId] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 




---- cmsStylesheetProperty ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsStylesheetProperty' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsStylesheetProperty] ADD CONSTRAINT [PK_cmsStylesheetProperty] PRIMARY KEY CLUSTERED ([nodeId] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 




---- cmsTab ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsTab' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsTab] ADD CONSTRAINT [PK_cmsTab] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 




---- cmsTagRelationship ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsTagRelationship' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsTagRelationship] ADD CONSTRAINT [PK_cmsTagRelationship] PRIMARY KEY CLUSTERED ([nodeId] ASC, [tagId] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 




---- cmsTags ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsTags' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsTags] ADD CONSTRAINT [PK_cmsTags] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- cmsTask ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsTask' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsTask] ADD CONSTRAINT [PK_cmsTask] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- cmsTaskType ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsTaskType' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsTaskType] ADD CONSTRAINT [PK_cmsTaskType] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
    CONSTRAINT [IX_cmsTaskType] UNIQUE NONCLUSTERED ([alias] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 




---- cmsTemplate ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'cmsTemplate' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [cmsTemplate] ADD CONSTRAINT [PK_templates] PRIMARY KEY CLUSTERED ([pk] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
    CONSTRAINT [IX_cmsTemplate] UNIQUE NONCLUSTERED ([nodeId] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- umbracoApp ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'umbracoApp' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [umbracoApp] ADD CONSTRAINT [PK_umbracoApp] PRIMARY KEY CLUSTERED ([appAlias] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 





---- umbracoAppTree ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'umbracoAppTree' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [umbracoAppTree] ADD CONSTRAINT [PK_umbracoAppTree] PRIMARY KEY CLUSTERED ([appAlias] ASC,  [treeAlias] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- umbracoDomains ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'umbracoDomains' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [umbracoDomains] ADD CONSTRAINT [PK_domains] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- umbracoLanguage ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'umbracoLanguage' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [umbracoLanguage] ADD CONSTRAINT [PK_language] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
    CONSTRAINT [IX_umbracoLanguage] UNIQUE NONCLUSTERED ([languageISOCode] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- umbracoLog ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'umbracoLog' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [umbracoLog] ADD CONSTRAINT [PK_umbracoLog] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- umbracoNode ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'umbracoNode' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [umbracoNode] ADD CONSTRAINT [PK_structure] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- umbracoRelation ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'umbracoRelation' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [umbracoRelation] ADD CONSTRAINT [PK_umbracoRelation] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 


---- umbracoRelationType ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'umbracoRelationType' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [umbracoRelationType] ADD CONSTRAINT [PK_umbracoRelationType] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- umbracoUser ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'umbracoUser' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [umbracoUser] ADD CONSTRAINT [PK_user] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
    CONSTRAINT [IX_umbracoUser] UNIQUE NONCLUSTERED ([userLogin] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 


---- umbracoUser2app ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'umbracoUser2app' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [umbracoUser2app] ADD CONSTRAINT [PK_user2app] PRIMARY KEY CLUSTERED ([user] ASC, [app] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- umbracoUser2NodeNotify ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'umbracoUser2NodeNotify' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [umbracoUser2NodeNotify] ADD CONSTRAINT [PK_umbracoUser2NodeNotify] PRIMARY KEY CLUSTERED ([userId] ASC, [nodeId] ASC, [action] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 



---- umbracoUser2NodePermission ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'umbracoUser2NodePermission' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [umbracoUser2NodePermission] ADD CONSTRAINT [PK_umbracoUser2NodePermission] PRIMARY KEY CLUSTERED ([userId] ASC, [nodeId] ASC, [permission] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 


---- umbracoUserLogins ----
-- No keys

---- umbracoUserType ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'umbracoUserType' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [umbracoUserType] ADD CONSTRAINT [PK_userType] PRIMARY KEY CLUSTERED ([id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END 









-- ###########################################################################################
-- # Foreign keys
-- ###########################################################################################


---- cmsContent ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsContent' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsContent]  WITH CHECK ADD  CONSTRAINT [FK_cmsContent_umbracoNode] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[umbracoNode] ([id])
END



---- cmsContentType ----
-- SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'cmsContentType' AND TABLE_SCHEMA ='dbo'
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsContentType' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsContentType]  WITH CHECK ADD  CONSTRAINT [FK_cmsContentType_umbracoNode] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[umbracoNode] ([id])

    -- Add temporarily, must be there so the upgrade scripts can remove this later on
    ALTER TABLE [dbo].[cmsContentType]  WITH CHECK ADD CONSTRAINT [DF_cmsContentType_masterContentType] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[umbracoNode] ([id])

    ALTER TABLE [dbo].[cmsContentType] CHECK CONSTRAINT [FK_cmsContentType_umbracoNode]

    ALTER TABLE [dbo].[cmsContentType] ADD  DEFAULT ('folder.png') FOR [thumbnail]

    ALTER TABLE [dbo].[cmsContentType] ADD  DEFAULT ((0)) FOR [masterContentType]
END 



---- cmsContentTypeAllowedContentType ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsContentTypeAllowedContentType' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsContentTypeAllowedContentType]  WITH CHECK ADD  CONSTRAINT [FK_cmsContentTypeAllowedContentType_cmsContentType] FOREIGN KEY([Id])
    REFERENCES [dbo].[cmsContentType] ([nodeId])

    ALTER TABLE [dbo].[cmsContentTypeAllowedContentType]  WITH CHECK ADD  CONSTRAINT [FK_cmsContentTypeAllowedContentType_cmsContentType1] FOREIGN KEY([AllowedId])
    REFERENCES [dbo].[cmsContentType] ([nodeId])
END 





---- cmsContentVersion ----
-- IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsContentVersion' AND TABLE_SCHEMA ='dbo')
-- BEGIN
--  ALTER TABLE [dbo].[cmsContentVersion]  WITH CHECK ADD  CONSTRAINT [FK_cmsContentVersion_cmsContent] FOREIGN KEY([ContentId])
--  REFERENCES [dbo].[cmsContent] ([nodeId])
-- END 



---- cmsContentXml ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsContentXml' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsContentXml]  WITH CHECK ADD  CONSTRAINT [FK_cmsContentXml_cmsContent] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[cmsContent] ([nodeId])
END 




---- cmsDataType ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsDataType' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsDataType]  WITH CHECK ADD  CONSTRAINT [FK_cmsDataType_umbracoNode] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[umbracoNode] ([id])
END 




---- cmsDataTypePreValues ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsDataTypePreValues' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsDataTypePreValues]  WITH CHECK ADD  CONSTRAINT [FK_cmsDataTypePreValues_cmsDataType] FOREIGN KEY([datatypeNodeId])
    REFERENCES [dbo].[cmsDataType] ([nodeId])
END 





---- cmsDocument ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsDocument' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsDocument]  WITH CHECK ADD  CONSTRAINT [FK_cmsDocument_cmsContent] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[cmsContent] ([nodeId])

    ALTER TABLE [dbo].[cmsDocument]  WITH CHECK ADD  CONSTRAINT [FK_cmsDocument_cmsTemplate] FOREIGN KEY([templateId])
    REFERENCES [dbo].[cmsTemplate] ([nodeId])

    ALTER TABLE [dbo].[cmsDocument]  WITH CHECK ADD  CONSTRAINT [FK_cmsDocument_umbracoNode] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[umbracoNode] ([id])
END 



---- cmsDocumentType ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsDocumentType' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsDocumentType]  WITH CHECK ADD  CONSTRAINT [FK_cmsDocumentType_cmsContentType] FOREIGN KEY([contentTypeNodeId])
    REFERENCES [dbo].[cmsContentType] ([nodeId])

    ALTER TABLE [dbo].[cmsDocumentType]  WITH CHECK ADD  CONSTRAINT [FK_cmsDocumentType_cmsTemplate] FOREIGN KEY([templateNodeId])
    REFERENCES [dbo].[cmsTemplate] ([nodeId])

    ALTER TABLE [dbo].[cmsDocumentType]  WITH CHECK ADD  CONSTRAINT [FK_cmsDocumentType_umbracoNode] FOREIGN KEY([contentTypeNodeId])
    REFERENCES [dbo].[umbracoNode] ([id])
END 




---- cmsLanguageText ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsLanguageText' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsLanguageText]  WITH CHECK ADD  CONSTRAINT [FK_cmsLanguageText_cmsDictionary] FOREIGN KEY([UniqueId])
    REFERENCES [dbo].[cmsDictionary] ([id])
END 



---- cmsMacroProperty ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsMacroProperty' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsMacroProperty]  WITH CHECK ADD  CONSTRAINT [FK_cmsMacroProperty_cmsMacro] FOREIGN KEY([macro])
    REFERENCES [dbo].[cmsMacro] ([id])

    ALTER TABLE [dbo].[cmsMacroProperty]  WITH CHECK ADD  CONSTRAINT [FK_umbracoMacroProperty_umbracoMacroPropertyType] FOREIGN KEY([macroPropertyType])
    REFERENCES [dbo].[cmsMacroPropertyType] ([id])
END 




---- cmsMember ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsMember' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsMember]  WITH CHECK ADD  CONSTRAINT [FK_cmsMember_cmsContent] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[cmsContent] ([nodeId])

    ALTER TABLE [dbo].[cmsMember]  WITH CHECK ADD  CONSTRAINT [FK_cmsMember_umbracoNode] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[umbracoNode] ([id])
END 




---- cmsMember2MemberGroup ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsMember2MemberGroup' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsMember2MemberGroup]  WITH CHECK ADD  CONSTRAINT [FK_cmsMember2MemberGroup_cmsMember] FOREIGN KEY([Member])
    REFERENCES [dbo].[cmsMember] ([nodeId])

    ALTER TABLE [dbo].[cmsMember2MemberGroup]  WITH CHECK ADD  CONSTRAINT [FK_cmsMember2MemberGroup_umbracoNode] FOREIGN KEY([MemberGroup])
    REFERENCES [dbo].[umbracoNode] ([id])
END 




---- cmsMemberType ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsMemberType' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsMemberType]  WITH CHECK ADD  CONSTRAINT [FK_cmsMemberType_cmsContentType] FOREIGN KEY([NodeId])
    REFERENCES [dbo].[cmsContentType] ([nodeId])

    ALTER TABLE [dbo].[cmsMemberType]  WITH CHECK ADD  CONSTRAINT [FK_cmsMemberType_umbracoNode] FOREIGN KEY([NodeId])
    REFERENCES [dbo].[umbracoNode] ([id])
END 




---- cmsPreviewXml ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsPreviewXml' AND TABLE_SCHEMA ='dbo')
BEGIN
--  ALTER TABLE [dbo].[cmsPreviewXml]  WITH CHECK ADD  CONSTRAINT [FK_cmsPreviewXml_cmsContent] FOREIGN KEY([nodeId])
--  REFERENCES [dbo].[cmsContent] ([nodeId])

    ALTER TABLE [dbo].[cmsPreviewXml]  WITH CHECK ADD  CONSTRAINT [FK_cmsPreviewXml_cmsContentVersion] FOREIGN KEY([versionId])
    REFERENCES [dbo].[cmsContentVersion] ([VersionId])
END 




---- cmsPropertyData ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsPropertyData' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsPropertyData]  WITH CHECK ADD  CONSTRAINT [FK_cmsPropertyData_cmsPropertyType] FOREIGN KEY([propertytypeid])
    REFERENCES [dbo].[cmsPropertyType] ([id])

    ALTER TABLE [dbo].[cmsPropertyData]  WITH CHECK ADD  CONSTRAINT [FK_cmsPropertyData_umbracoNode] FOREIGN KEY([contentNodeId])
    REFERENCES [dbo].[umbracoNode] ([id])
END 




---- cmsPropertyType ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsPropertyType' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsPropertyType]  WITH CHECK ADD  CONSTRAINT [FK_cmsPropertyType_cmsContentType] FOREIGN KEY([contentTypeId])
    REFERENCES [dbo].[cmsContentType] ([nodeId])

    ALTER TABLE [dbo].[cmsPropertyType]  WITH CHECK ADD  CONSTRAINT [FK_cmsPropertyType_cmsDataType] FOREIGN KEY([dataTypeId])
    REFERENCES [dbo].[cmsDataType] ([nodeId])

    ALTER TABLE [dbo].[cmsPropertyType]  WITH CHECK ADD  CONSTRAINT [FK_cmsPropertyType_cmsTab] FOREIGN KEY([tabId])
    REFERENCES [dbo].[cmsTab] ([id])
END 




---- cmsStylesheet ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsStylesheet' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsStylesheet]  WITH CHECK ADD  CONSTRAINT [FK_cmsStylesheet_umbracoNode] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[umbracoNode] ([id])
END 




---- cmsStylesheetProperty ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsStylesheetProperty' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsStylesheetProperty]  WITH CHECK ADD  CONSTRAINT [FK_cmsStylesheetProperty_umbracoNode] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[umbracoNode] ([id])
END 




---- cmsTab ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsTab' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsTab]  WITH CHECK ADD  CONSTRAINT [FK_cmsTab_cmsContentType] FOREIGN KEY([contenttypeNodeId])
    REFERENCES [dbo].[cmsContentType] ([nodeId])
END 




---- cmsTagRelationship ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsTagRelationship' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsTagRelationship]  WITH CHECK ADD  CONSTRAINT [cmsTags_cmsTagRelationship] FOREIGN KEY([tagId])
    REFERENCES [dbo].[cmsTags] ([id])
    ON DELETE CASCADE

    ALTER TABLE [dbo].[cmsTagRelationship]  WITH CHECK ADD  CONSTRAINT [umbracoNode_cmsTagRelationship] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[umbracoNode] ([id])
    ON DELETE CASCADE
END 



---- cmsTask ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsTask' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsTask]  WITH CHECK ADD  CONSTRAINT [FK_cmsTask_cmsTaskType] FOREIGN KEY([taskTypeId])
    REFERENCES [dbo].[cmsTaskType] ([id])

    ALTER TABLE [dbo].[cmsTask]  WITH CHECK ADD  CONSTRAINT [FK_cmsTask_umbracoNode] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[umbracoNode] ([id])

    ALTER TABLE [dbo].[cmsTask]  WITH CHECK ADD  CONSTRAINT [FK_cmsTask_umbracoUser] FOREIGN KEY([parentUserId])
    REFERENCES [dbo].[umbracoUser] ([id])

    ALTER TABLE [dbo].[cmsTask]  WITH CHECK ADD  CONSTRAINT [FK_cmsTask_umbracoUser1] FOREIGN KEY([userId])
    REFERENCES [dbo].[umbracoUser] ([id])
END 


---- cmsTemplate ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'cmsTemplate' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[cmsTemplate]  WITH CHECK ADD  CONSTRAINT [FK_cmsTemplate_cmsTemplate] FOREIGN KEY([master])
    REFERENCES [dbo].[cmsTemplate] ([nodeId])

    ALTER TABLE [dbo].[cmsTemplate]  WITH CHECK ADD  CONSTRAINT [FK_cmsTemplate_umbracoNode] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[umbracoNode] ([id])
END 



---- umbracoAppTree ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'umbracoAppTree' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[umbracoAppTree]  WITH CHECK ADD  CONSTRAINT [FK_umbracoAppTree_umbracoApp] FOREIGN KEY([appAlias])
    REFERENCES [dbo].[umbracoApp] ([appAlias])
END 



---- umbracoDomains ----
-- IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'umbracoDomains' AND TABLE_SCHEMA ='dbo')
-- BEGIN
--  ALTER TABLE [dbo].[umbracoDomains]  WITH CHECK ADD  CONSTRAINT [FK_umbracoDomains_umbracoNode] FOREIGN KEY([domainRootStructureID])
--  REFERENCES [dbo].[umbracoNode] ([id])
-- END 





---- umbracoNode ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'umbracoNode' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[umbracoNode]  WITH CHECK ADD  CONSTRAINT [FK_umbracoNode_umbracoNode] FOREIGN KEY([parentID])
    REFERENCES [dbo].[umbracoNode] ([id])
END 



---- umbracoRelation ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'umbracoRelation' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[umbracoRelation]  WITH CHECK ADD  CONSTRAINT [FK_umbracoRelation_umbracoNode] FOREIGN KEY([parentId])
    REFERENCES [dbo].[umbracoNode] ([id])

    ALTER TABLE [dbo].[umbracoRelation]  WITH CHECK ADD  CONSTRAINT [FK_umbracoRelation_umbracoNode1] FOREIGN KEY([childId])
    REFERENCES [dbo].[umbracoNode] ([id])

    ALTER TABLE [dbo].[umbracoRelation]  WITH CHECK ADD  CONSTRAINT [FK_umbracoRelation_umbracoRelationType] FOREIGN KEY([relType])
    REFERENCES [dbo].[umbracoRelationType] ([id])
END 



---- umbracoUser ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'umbracoUser' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[umbracoUser]  WITH CHECK ADD  CONSTRAINT [FK_user_userType] FOREIGN KEY([userType])
    REFERENCES [dbo].[umbracoUserType] ([id])
END 




---- umbracoUser2app ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'umbracoUser2app' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[umbracoUser2app]  WITH CHECK ADD  CONSTRAINT [FK_umbracoUser2app_umbracoApp] FOREIGN KEY([app])
    REFERENCES [dbo].[umbracoApp] ([appAlias])

    ALTER TABLE [dbo].[umbracoUser2app]  WITH CHECK ADD  CONSTRAINT [FK_umbracoUser2app_umbracoUser] FOREIGN KEY([user])
    REFERENCES [dbo].[umbracoUser] ([id])
END 




---- umbracoUser2NodeNotify ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'umbracoUser2NodeNotify' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[umbracoUser2NodeNotify]  WITH CHECK ADD  CONSTRAINT [FK_umbracoUser2NodeNotify_umbracoNode] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[umbracoNode] ([id])

    ALTER TABLE [dbo].[umbracoUser2NodeNotify]  WITH CHECK ADD  CONSTRAINT [FK_umbracoUser2NodeNotify_umbracoUser] FOREIGN KEY([userId])
    REFERENCES [dbo].[umbracoUser] ([id])
END 




---- umbracoUser2NodePermission ----
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'umbracoUser2NodePermission' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[umbracoUser2NodePermission]  WITH CHECK ADD  CONSTRAINT [FK_umbracoUser2NodePermission_umbracoNode] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[umbracoNode] ([id])

    ALTER TABLE [dbo].[umbracoUser2NodePermission]  WITH CHECK ADD  CONSTRAINT [FK_umbracoUser2NodePermission_umbracoUser] FOREIGN KEY([userId])
    REFERENCES [dbo].[umbracoUser] ([id])
END 

Cheers

I'm in the same position so thanks for sharing, but after running your script I get this

The database configuration failed with the following message: 'FK_umbracoUser2app_umbracoApp' is not a constraint. Could not drop constraint. See previous errors. Please check log file for additional information (can be found in '/App_Data/Logs/UmbracoTraceLog.txt')

Do you know what that is about?

Kim, are you also doing an upgrade from 4.9 to 6.1.1?

Yes unfortunately and that's even with a uCommerce installed, I have tried everything without success. Now I'm considering to install a clean Umbraco 6x with a clean ucommerce and exporting the data.

It's going to take a long time but do not think there any way around it ...

It sounds like you run the script listed above and then go through the installer to finalize the upgrade, but that won't work since the upgrade process assumes your db schema to be that of 4.9 - which removes the constraint that you mentioned above.

Unless you had the exact same database issue as Eric, who had a database with missing constraints I'd be interested in knowing what the upgrade issue was. Mostly because the upgrade process will work if your schema is actually that of 4.9. If its been through a bunch of upgrades of various v4 upgrades there is a good chance something specific is missing.

- Morten

Kim, if FK_umbracoUser2app_umbracoApp is missing you must add it to perform the upgrade. I would adivice you to backup your existing database before you continue.

If you look in a 4.9.something database there should be some keys, if you are missing FK_umbracoUser2app_umbracoApp you are most likely also missing FK_umbracoUser2app_umbracoUser. A visual check will confirm this. The image illustrates how a 4.9 db should look

To insert booth keys run something like this

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'umbracoUser2app' AND TABLE_SCHEMA ='dbo')
BEGIN
    ALTER TABLE [dbo].[umbracoUser2app]  WITH CHECK ADD  CONSTRAINT [FK_umbracoUser2app_umbracoApp] FOREIGN KEY([app])
    REFERENCES [dbo].[umbracoApp] ([appAlias])

    ALTER TABLE [dbo].[umbracoUser2app]  WITH CHECK ADD  CONSTRAINT [FK_umbracoUser2app_umbracoUser] FOREIGN KEY([user]
   REFERENCES [dbo].[umbracoUser] ([id])
END 

When done you should have the keys added

Let us know how it goes

Thanks for your answer, I decided to install a umbraco 6 with the latest uCommerce  and moved all the content and products. 2 days but now I'm ready to go with the latest versions.

Hello - I am trying to upgrade from 4.5.2 to 6.1.3 - I first got the error:

There are no primary or candidate keys in the referenced table 'cmsPropertyTypeGroup' that match...

but I did not have the key umbracoConfigurationStatus set in my web.config , so I set it to 4.5.2. 

Started the install again and then got:

'FK_umbracoUser2app_umbracoApp' is not a constraint. Could not drop constraint.

tried to run the sql script provided by Eric.. (thank you)..

but I get this error:

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'REFERENCES'.

I am not familiar with REFERENCES so I am looking into it. but any help is appreciated.

Thanks

Doh. the script is missing an ")" after user.

anyway I still get an error:

There are no primary or candidate keys in the referenced table 'dbo.umbracoApp' that match the referencing column list in the foreign key 'FKumbracoUser2appumbracoApp'.

The error is most likely because the foreign key "FKumbracoUser2appumbracoApp" never existed in your database. If you create it before running the upgrade it will properly work. At least its worth a try.

• Morten

Thanks for the reply Morten... I just realized that when I imported the database to my test server it did not import PK's and FK's....

That may be the problem.. LOL.

Thank you

is working on a reply...