MigrationGenerator.DropColumn does not take unique index (maybe all indices?) into account
Arnagos opened this issue · 8 comments
First: Thanks for your work fixing all of these issues!
Dropping a unique foreign key column in MSSQL results in an SQL exception.
Column:
Index:
Generated SQL:
ALTER TABLE [dbo].[Punchclock__Company] DROP CONSTRAINT [Punchclock__Company_Own_Contact]
ALTER TABLE [dbo].[Punchclock__Company] DROP COLUMN [ContactInfoId];
Exception:
The index 'Punchclock__Company_ContactInfoId_idx' is dependent on column 'ContactInfoId'.
ALTER TABLE DROP COLUMN ContactInfoId failed because one or more objects access this column.
I created a local NuGet package with your changes and tried it out. Works perfectly fine.
Aah, I was wrong. There is a bug in this. The new code does not remove the constraints and indices that it dropped from the table and generates SQL like this:
DROP INDEX [Punchclock__Company_ContactInfoId_idx] ON [dbo].[Punchclock__Company];
ALTER TABLE [dbo].[Punchclock__Company] DROP CONSTRAINT [Punchclock__Company_Own_Contact]
ALTER TABLE [dbo].[Punchclock__Company] DROP COLUMN [ContactInfoId];
ALTER TABLE [dbo].[Punchclock__Company] DROP CONSTRAINT [Punchclock__Company_Own_Contact];
DROP INDEX [Punchclock__Company_ContactInfoId_idx] ON [dbo].[Punchclock__Company];
After the column comparison stage, it gets to the index and constraint comparisons and since the model still contains these entries he tries to drop them again.
(I'm using CompareSchemas for this). If you don't want to change the schema the easiest way might be to just add an "IF EXISTS" to the drops executed later.
The order of operations in CompareSchemas for dropping columns is the opposite to adding columns, so this is problematic - but at least for sqlServer, IF EXISTS neatly avoids an error. Fortunately dropping columns is rare- esp if they have indexes/constraints - tables tend to grow, not shrink. In years of use of this, I've never found this bug before !
Do you have an ETA for a new release on NuGet?
https://www.nuget.org/packages/DatabaseSchemaReader/ Just released!
Are you sure that you released the correct version? DatabaseIndex does not contain the Filter property in this release.
I had a small technical update anyway, so there's a new nuget package now.
Working fine now, thanks!