I found where the problems exist in the upgrade script and fixed them myself in my database. They are below. I'll still be happy to test the next version of your upgrade script if you'd still like.
For these errors:
Error in file:C:\inetpub\wwwroot\BugNet\Install\dbscripts\0.7.936.0.SqlDataProvider.sql
Message:The constraint 'PK__Component__09DE7BCC' is being referenced by table 'Bug', foreign key constraint 'FK_Bug_Component'. Could not drop constraint. See previous errors.
Error in file:C:\inetpub\wwwroot\BugNet\Install\dbscripts\0.7.936.0.SqlDataProvider.sql
Message:Table 'Component' already has a primary key defined on it. Could not create constraint. See previous errors.
There seems to be no reason to do the following statements. If they were taken out it wouldn't error.
LINE 496
ALTER TABLE [dbo].[Component] DROP CONSTRAINT [PK__Component__09DE7BCC]
LINE 770
ALTER TABLE [dbo].[Component] ADD
CONSTRAINT [PK_Component] PRIMARY KEY CLUSTERED
(
[ComponentID]
) ON [PRIMARY];
For this error:
Error in file:C:\inetpub\wwwroot\BugNet\Install\dbscripts\0.7.936.0.SqlDataProvider.sql
Message:The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_UserProjects_aspnet_Users". The conflict occurred in database "BugNet", table "dbo.aspnet_Users", column 'UserId'.
The data just needs cleaned up. The select statement will show the bad data. The delete will clean the data up.
SELECT * FROM UserProjects WHERE UserId NOT IN (SELECT UserId FROM aspnet_Users)
--DELETE FROM UserProjects WHERE UserId NOT IN (SELECT UserId FROM aspnet_Users)
For this error:
Error in file:C:\inetpub\wwwroot\BugNet\Install\dbscripts\0.7.936.0.SqlDataProvider.sql
Message:The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Bug_FixedInVersionId". The conflict occurred in database "BugNet", table "dbo.Version", column 'VersionID'.
The data just needs cleaned up. The select statement will show the bad data. The delete will clean the data up.
SELECT * FROM Bug WHERE FixedInVersionId NOT IN (SELECT VersionID FROM Version)
--UPDATE Bug SET FixedInVersionId = (SELECT TOP 1 VersionID FROM Version WHERE ProjectID = Bug.ProjectID) WHERE FixedInVersionId = -1
For this error:
Error in file:C:\inetpub\wwwroot\BugNet\Install\dbscripts\0.7.936.0.SqlDataProvider.sql
Message:Introducing FOREIGN KEY constraint 'FK_Component_Project' on table 'Component' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors.
The alter constraint just doesn't need the 'ON UPDATE NO ACTION ON DELETE CASCADE' and it works fine to add the contraint
LINE 1146
ALTER TABLE [dbo].[Component] WITH NOCHECK ADD
CONSTRAINT [FK_Component_Project] FOREIGN KEY
(
[ProjectID]
)REFERENCES [dbo].[Project](
[ProjectID]
) ON UPDATE NO ACTION ON DELETE CASCADE
For this error:
Error in file:C:\inetpub\wwwroot\BugNet\Install\dbscripts\0.7.936.0.SqlDataProvider.sql
Message:The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_UserRoles_aspnet_Users". The conflict occurred in database "BugNet", table "dbo.aspnet_Users", column 'UserId'.
Again the data is bad and needs cleaned up. The select statement will show the bad data. The delete will clean the data up.
--select * from UserRoles where UserId not in (select UserId from aspnet_Users)
delete from UserRoles where UserId not in (select UserId from aspnet_Users)