CODEWITHSUNDEEP
asp.net-mvcentity-frameworkentity-framework-migrations
Tomi Lammi
Tomi Lammi

Reputation: 2126

Entity-framework code-first migrations NOCHECK constraint?

Is it possible to alter tables in code-first migrations with nocheck constraints? I haven't found any way to do this except of getting SQL script via Update-Database -Script and modifying the sql statements.

Upvotes: 2

Views: 6479

Answers (3)

David S.
David S.

Reputation: 31

If the goal is to create a required foreign key but update-database fails due to existing data, just do it in two stages. For example, suppose I want to add a foreign key for TEAM in a PLAYER class for a 1:m relationship where all players must be assigned to a team. Assuming all classes have integer identity primary keys... 1. Add the usual two properties to PLAYER but use a nullable int for the foreign key. ie:

    public int? TeamID { get; set; }

    public virtual Team Team { get; set; }

  1. Add a migration and update the database. All of the TeamID values will be null but you will be allowed to create the relationship.

    1. Next, fix your data so every player is assigned a valid TeamID. If you are seeding player data, you will need to supply TeamID values there as well.

    2. Modify the PLAYER class so the int is no longer nullable:

      public int TeamID { get; set; }

    3. Add another migration and update the database again. You should be good to go.

Upvotes: 3

Ken Smith
Ken Smith

Reputation: 20445

Another way to do it for one-off occasions is to call the SQL for creating the FK manually, like so, from within your Up() method:

// AddForeignKey("dbo.EFElementGroupEntries", "ConstraintCode", "dbo.EFElementConstraints", "Code");
Sql(@"ALTER TABLE [dbo].[EFElementGroupEntries]  WITH NOCHECK 
        ADD CONSTRAINT[FK_dbo.EFElementGroupEntries_dbo.EFElementConstraints_ConstraintCode] FOREIGN KEY([ConstraintCode])
        REFERENCES[dbo].[EFElementConstraints]([Code])");
Sql(@"ALTER TABLE [dbo].[EFElementGroupEntries] CHECK CONSTRAINT [FK_dbo.EFElementGroupEntries_dbo.EFElementConstraints_ConstraintCode]");

Upvotes: 4

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364269

You can try creating your own MigrationSqlGenerator and use it with migrations (you can set custom SQL generator in DbMigrationsConfiguration) - I think it should be enough to inherit SqlServerMigrationSqlGenerator and add NOCHECK after adding foreign key = overriding Generate(AddForeignKeyIperation operation).

Anyway using NOCHECK with EF is way to disaster. If you want to have database without checked constraints don't use EF because it will crash every time it reaches inconsistency.

Upvotes: 6

Related Questions