Query:
ALTER TABLE [CMS_Tree] ALTER COLUMN [NodeParentID] [int] NULL;
UPDATE [CMS_Tree] SET [NodeParentID] = NULL WHERE [NodeParentID] = 0
DECLARE
@NodeID int,
@NodeGUID uniqueidentifier,
@NodeSiteID int;
DECLARE @cursor CURSOR;
DECLARE @orphans TABLE (
NodeID int,
NodeGUID uniqueidentifier,
NodeSiteID int
);
INSERT INTO @orphans SELECT DISTINCT NodeID, NodeGUID, NodeSiteID FROM CMS_Tree WHERE NodeParentID NOT IN (SELECT NodeID FROM CMS_Tree)
SET @cursor = CURSOR FOR SELECT NodeID, NodeGUID, NodeSiteID FROM @orphans;
OPEN @cursor
FETCH NEXT FROM @cursor INTO @NodeID, @NodeGUID, @NodeSiteID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC Proc_CMS_Tree_RemoveDependencies @NodeID, @NodeGUID, @NodeSiteID
DELETE FROM CMS_Document WHERE DocumentNodeID = @NodeID
DELETE FROM CMS_Tree WHERE NodeID = @NodeID
FETCH NEXT FROM @cursor INTO @NodeID, @NodeGUID, @NodeSiteID
END
CLOSE @cursor;
DEALLOCATE @cursor;
ALTER TABLE [CMS_Tree] WITH CHECK ADD CONSTRAINT [FK_CMS_Tree_NodeParentID_CMS_Tree] FOREIGN KEY([NodeParentID]) REFERENCES [CMS_Tree] ([NodeID])
ALTER TABLE [CMS_Tree] CHECK CONSTRAINT [FK_CMS_Tree_NodeParentID_CMS_Tree]
ALTER TABLE [CMS_Tree] DROP CONSTRAINT [FK_CMS_Tree_CMS_Tree]
Caused exception:
The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_DocumentCategory_DocumentID_CMS_Document". The conflict occurred in database "my_DB", table "dbo.CMS_DocumentCategory", column 'DocumentID'.
The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_Document_DocumentNodeID_CMS_Tree". The conflict occurred in database "my_DB", table "dbo.CMS_Document", column 'DocumentNodeID'.
The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_Attachment_AttachmentDocumentID_CMS_Document". The conflict occurred in database "my_DB", table "dbo.CMS_Attachment", column 'AttachmentDocumentID'.
The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_Document_DocumentNodeID_CMS_Tree". The conflict occurred in database "my_DB", table "dbo.CMS_Document", column 'DocumentNodeID'.
The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_Attachment_AttachmentDocumentID_CMS_Document". The conflict occurred in database "my_DB", table "dbo.CMS_Attachment", column 'AttachmentDocumentID'.
The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_Document_DocumentNodeID_CMS_Tree". The conflict occurred in database "my_DB", table "dbo.CMS_Document", column 'DocumentNodeID'.
The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_Attachment_AttachmentDocumentID_CMS_Document". The conflict occurred in database "my_DB", table "dbo.CMS_Attachment", column 'AttachmentDocumentID'.
The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_Document_DocumentNodeID_CMS_Tree". The conflict occurred in database "my_DB", table "dbo.CMS_Document", column 'DocumentNodeID'.
The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_Attachment_AttachmentDocumentID_CMS_Document". The conflict occurred in database "my_DB", table "dbo.CMS_Attachment", column 'AttachmentDocumentID'.
The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_Document_DocumentNodeID_CMS_Tree". The conflict occurred in database "my_DB", table "dbo.CMS_Document", column 'DocumentNodeID'.
The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_Attachment_AttachmentDocumentID_CMS_Document". The conflict occurred in database "my_DB", table "dbo.CMS_Attachment", column 'AttachmentDocumentID'.
HOW TO CORRECT THESE ERRORS.
THANK YOU FOR HELPING US