1

Context

ASP.NET application with EF Core 6, connecting to a SQL Server database.

The current application I'm working on makes use of a database with about 200 tables.

Recently, the need has arisen for our application to store Unicode characters. This means all of those tables need to have several of their columns' types changed from VarChar (some char) to NVarChar.

Many of those tables use Char columns as primary key. Since many tables are linked, this means there are many Char FK columns as well.

I managed to change the EF code to generate the migration which would change these types.

Edit: For some added context: the database gets used in an existing (production) application, which means any alterations are limited to those things that would impact functionality (changing a column to a similar type is OK, changing a name is not).

Issue

Unfortunately, the generated migration fails because it would alter PK and FK columns

SqlException The object 'FK__FOREIGN_KEY_NAME' is dependent on column 'COLUMN_NAME'

The most common solution suggested online is to change the migration to drop keys before altering a column, and restore them again after. Because of the size and complexity of the database, this would take a lot of time and effort, so I'm trying to avoid this route.

Are there any other possibilities to change column types while ignoring any constraints?

I've tried

  • removing keys in code, both in OnModelCreate and attributes in the entity classes. This was a tedious job, which only resulted in an error because of the lack of keys.
  • disabling constraints using ALTER TABLE ? NOCHECK CONSTRAINT ALL, as described in this question. This did not prevent the aforementioned errors.
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Stefanovix
  • 11
  • 3
  • 1
    Unfortunately, i think you're smoked. But if you use foreign keys to connect any kind of varchar data that is unicode dependant (or have it as PKs even), i think you're not doing the right thing anyway, so maybe it's a blessing in disguise to just drop these keys and create proper ones. What kind of data are you storing as PKs that has need to unicode? A dictionary? – siggemannen Mar 20 '23 at 13:37
  • Yeah, I'm aware the design is not quite optimal (to put it mildly), so I was kind of afraid this would be the answer. The system is used for several kinds of business objects (materials, recipes, products...), all entered by the users. We're introducing our application in several Asian branches, which is why we need to enable unicode fields. The old version of the application uses the first letters of the objects' description to generate the PK field. – Stefanovix Mar 20 '23 at 13:50
  • Have you considered creating a new database with the correct schema, slowly background sync the production database across to it, then switch over when done? Since your system is full of character-based primary keys you're not going to run into the `int identity` problems that most databases would face when trying to sync like this. – AlwaysLearning Mar 20 '23 at 21:35

0 Answers0