SqlServer Migrations: Rebuild primary keys when column collation changes

[zmhh]

File a bug

Migrating a column collation fails when the column is used as a key, with the error:

System.Data.SqlClient.SqlException (0x80131904): The object 'PK_[object]' is dependent on column '[column]'.
ALTER TABLE ALTER COLUMN [column] failed because one or more objects access this column.

EF Core version: 5.0.9
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
Operating system: Windows

[roji]

Confirmed:

CREATE TABLE foo (a NVARCHAR(50) PRIMARY KEY);
ALTER TABLE foo ALTER COLUMN a NVARCHAR(50) COLLATE Cyrillic_General_CI_AS; -- fails

However, this seems to be SQL Server-specific - this works on PostgreSQL:

CREATE COLLATION some_collation (LC_COLLATE = 'en-u-ks-primary',
    LC_CTYPE = 'en-u-ks-primary',
    PROVIDER = icu,
    DETERMINISTIC = False
);

CREATE TABLE foo (a TEXT PRIMARY KEY);
ALTER TABLE foo ALTER COLUMN a TYPE text COLLATE "some_collation"; -- works

There's a chance this is a general limitation around altering columns (i.e. not just collation).

I don't think there's much we can do here, unless we want to drop and recreate the primary key constraint (and all foreign keys pointing to it).

[zmhh]

It would be nice if EF dropped and recreated constraints as needed.

[roji]

Triage decision: keep open to track dropping and recreating the primary key constraint (similar to #16758 and possibly other cases).