search

JasperFx / weasel

Database Development Made Easy for .Net
MIT License
65 stars 19 forks source link

Unable to drop PK constraint during migration #42

Closed tonykaralis closed 2 years ago

tonykaralis commented 2 years ago

Follow on rom #39.

Migrations are being generated when primary key constraint names have changed. When the constraint is not used in another table the migrations complete successfully, however when a constraint is used in another table the migrations fail. This is due to using alter table with drop and then add commands. An alternative would be to use alter table with rename command.

Things to consider: The current commands work well as they cover scenarios where the actual primary key column name changes, whereas this bug occurs when the PK constraint name changes. A rename might work for constraints but not for the actual column.

tonykaralis commented 2 years ago

Update: DROP commands (without CASCADE) wont work on PKs that are used as FKs in other tables.

writer.WriteLine($"alter table {Expected.Identifier} drop constraint if exists {Expected.PrimaryKeyName};");
writer.WriteLine($"alter table {Expected.Identifier} add {Actual.PrimaryKeyDeclaration()};");

We therefore have 2 bugs that are a result of the above commands:

  1. When only a PK constraint name changes that is also used as a a FK - drop commands fail (relevant to Marten v4 migrations see https://github.com/JasperFx/marten/issues/1940 )
  2. When we actually need to change the PK column, and the PK is used as a FK - drop commands fail (relevant to anyone wanting to actually change their PK columns.
oskardudycz commented 2 years ago

@tonykaralis released in 1.0.4 👍