Sql Server Migrations: Throw when altering IDENTITY

[rowanmiller]

If you go through the process of changing the ApplicationUser ID type to int, then you will hit issues with EF migrations. On SQL Server, you'll get errors including:

The object 'PK_AspNetUsers' is dependent on column 'Id'.
The object 'FK_AspNetUserRoles_AspNetUsers_UserId' is dependent on column 'Id'.
The object 'FK_AspNetUserLogins_AspNetUsers_UserId' is dependent on column 'Id'.
The object 'FK_AspNetUserClaims_AspNetUsers_UserId' is dependent on column 'Id'.
ALTER TABLE ALTER COLUMN Id failed because one or more objects access this column.

[flagbug]

@rowanmiller What would be the best way to do this right now?

[bricelam]

This will require table rebuilds (issue #329). Even if we managed to drop and recreate all the referencing constraints, the updated primary key columns will not be marked as IDENTITY.

The best workaround for now is to make this change before creating the database, delete the model snapshot and migrations files, then re-add the CreateIdentitySchema migration.

[divega]

Triage: the reson this was traiged for 1.1.0 is that we want to give users more information when this situation happens.

[bricelam]

I'm still not sure how to provide additional information in this scenario.

Options:

• Detect the dependent constraints. After doing this, we might as well just rebuild them.
• Detect that IDENTITY has been added. This will require knowledge of the previous model (also required for SQL Server Migrations: Rebuild indices on narrowed columns #6102)

Invalid options:

• Throw when altering with IDENTITY. This is valid if the column was added with IDENTITY.
• Throw when altering a key. This is valid provided you manually rebuild the dependent constraints.