0

Running EXEC sp_msforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL" will disable Foreign keys on existing tables.

What if the tables and insert data queries that enforce foreign key constraints run after this query,?

I am encountering this issue during build automation and What I am ideally look for is a permanent switch to disable all constraints on the database (i can do that since the database is created as a part of build process).

NOTE: See the 5 steps mentioned towards the last to get an idea of the issue faced during build automation

I have created a build step before processing the scripts to disable all existing foreign key constraints. The next step would be package and run all release sql scripts that may contain tables created, data inserted. The earlier build step to disable constraints have no clue about forth coming database tables and insert scripts which will enforce foreign key constraints after running the data insert, failing my build process.

Is there a way i am set a flag in the database to stop checking for foreign keys?

Adding some more context to what i am doing specifically.Automating build using bamboo and following steps are performed on a high level

  1. locate last available deployed db schema

  2. build a database using the schema generated script (no master data copied).

  3. disable all foreign keys (unable to disable FK for tables yet to be created in next step)

  4. merge all release specific db scripts(may contain new db and insert scripts)

  5. apply other transformations like running codegeneration, script compare, delta finding etc.

Step 3 is the challenge.

Note: This is automating a legacy system with 300ish master datables and data, since Codesmith tools are used, schema changes has to be detected and auto generated code has to be checked against last deployed schema. Since the master data is so huge, keeping a reference db with data for build purposes is out of the question hence the referential integrity constraint issue will be more prominent.

Lin
  • 633
  • 8
  • 26
  • 1
    why do you want to disable foreign key constraint in the first place ? – Squirrel Aug 04 '17 at 02:22
  • Because the database created on the fly for build purpose will not have master data and developer created insert scripts will check constraints after inserting data – Lin Aug 04 '17 at 02:35
  • 1
    Possible duplicate of [How to disable Constraints for all the tables and enable it?](https://stackoverflow.com/questions/14972816/how-to-disable-constraints-for-all-the-tables-and-enable-it) – gview Aug 04 '17 at 02:47
  • it is not duplicate. that question deals with disable existing tables not the tables that will be created later in the chain – Lin Aug 04 '17 at 03:58

2 Answers2

1

The only thing I can think of is to create a DDL trigger which listens for constraints' creation and, if any are detected, drops them. However, I'm not sure this approach is viable if a constraint is created as a part of the create table statement. You should test it thoroughly before using.

Personally, however, I usually solve this by properly ordering the sequence in which the data is inserted. It's much safer, not prohibitively difficult and, last but not least, always possible to do.

Roger Wolf
  • 7,307
  • 2
  • 24
  • 33
  • Your idea about DDL trigger seems to be the only feasible option here, and It works.I am creating a Database DDL trigger on the fly and dropping it after the scripts are run. Trigger is looking for all DDL operations on Tables,Functions and Procedures. I am able to extract schema and object details from EVENTDATA() so all good. thanks again for the TIP. – Lin Aug 06 '17 at 21:56
0

Your basic problem is that your database migrations that are creating your database are running in the wrong order. Adjust the order of tables and data insertion so that only data that references already existing data, is inserted at any one time

Turning all the constraints off, loading data, and turning them all back on at the start and end of each script that does DB data alterations, is also an option, but you should separate your scripts that do schema changes from your scripts that do data loading and run all the schema changes first

Caius Jard
  • 72,509
  • 5
  • 49
  • 80