During the BlogEngine upgrade from 2.0 to 2.5 (one that hosts this blog) I’ve come across a problem. The problem might happen during execution of the SQL Server upgrade scripts that come with BlogEngine 2.5.
After running the scripts I’ve got an error mentioning that constraint FK_be_PostComment_be_Posts can’t be enforced. Huh? After some experimenting I’ve seen that the 2.0 database isn’t exactly well enforced with constraints and I had some comments left that don’t belong to any of the posts (I guess I’ve deleted the posts but comments were still there because database didn’t enforce the constraints and BlogEngine didn’t delete them).
Here is what I’ve did to upgrade.
1. In the upgrade script comment this statement:
1: ALTER TABLE dbo.be_PostComment
2: ADD CONSTRAINT FK_be_PostComment_be_Posts FOREIGN KEY (BlogID, PostID) REFERENCES dbo.be_Posts (BlogID, PostID)
3: GO
2. Run the upgrade script.
3. (Optional) To find if any comment is parentless execute this SELECT statement
1: SELECT * FROM dbo.be_PostComment WHERE postid NOT IN (SELECT postid FROM be_posts)
4. Delete the orphaned comments
1: DELETE FROM dbo.be_PostComment WHERE postid NOT IN (SELECT postid FROM be_posts)
5. Run the statement from upgrade script that you’ve commented in #1.
That’s it. I guess you could delete orphaned comments even before running the upgrade script and thus avoid first and last step.
Observation: Looks like at least database in version 2.0 wasn’t very well enforced, hopefully 2.5 rectifies this problem (it adds constrains here and there). Don’t forget, database is the last defense tier against bad data and should be as much protected as it can be.