locked
CodeFirst Foreign Keys "Not for Replication" RRS feed

  • Question

  • I would like to know if there is a way to mark the foreign keys in my model as Not for Replication so that they show in the database as Enforce for Replication = false.

    Currently, we are dropping every foreign key and re-adding them manually with the ATLER TABLE statement and want to know if there is a better way?

    IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = N'AccountRequest_GeographicEntity')  ALTER TABLE [dbo].[AccountRequest] DROP CONSTRAINT [AccountRequest_GeographicEntity]; 
    ALTER TABLE [dbo].[AccountRequest]  WITH CHECK ADD CONSTRAINT [AccountRequest_GeographicEntity] FOREIGN KEY([GeographicEntityID]) REFERENCES [dbo].[GeographicEntity] ([GeographicEntityID]) NOT FOR REPLICATION;

    

    

    Thursday, January 7, 2016 4:25 PM

Answers

  • You can continue to do what you are doing, although rechecking the constraint during a migration might be costly if your tables are large.

    But when you start doing DBA stuff to your database, it's probably time to switch your workflow from "Code First" to "Code First to an Existing Database". And manage your database schema with SSDT.

    And in particular, if you enable transactional replication you need to be carefull how you make design changes, as only certain DDL operations will replicate to subscribers, and Code First Migrations may just not work.

    see

    Make Schema Changes on Publication Databases

    David


    David http://blogs.msdn.com/b/dbrowne/


    Thursday, January 7, 2016 4:53 PM