SQL Script to alter ALL Foreign Keys to add ON DELETE CASCADE

Question

I have a SQL 2005 database with approx 250 tables.

I want to temporarily enable ON DELETE CASCADE to all of the Foreign Keys so that I can do a bulk delete easily.

I then want to turn off ON DELETE CASCADE on all Foreign Keys.

The only way I know of doing this, is to use Management Studio to generate a full database create script, do some kind of search and replace to strip out everything but Foreign Keys, save the script, then do some more search and replacing to add the ON DELETE CASCADE.

Then I run the script, do my delete, and then run the other script.

Is there an easier way to produce this script? This method seems far too prone to error and I will have to keep the script up to date with any other changes we make to the database, or re-generate it manually each time I may need to use it.

Is an alternative option to run a select on the system tables to "generate" the script for me? Could it even be possible to run an update on a system table that enables and disables ON DELETE CASCADE?

Answer 1

You'll have to to alter the table, drop FK constraints and recreate them:

This is db2 syntax. SQLServer should be similar

ALTER TABLE emp DROP CONSTRAINT fk_dept;

ALTER TABLE emp ADD CONSTRAINT fk_dept
FOREIGN KEY(dept_no)
REFERENCES dept(deptno)
ON DELETE CASCADE;

You can write your own sp to query the system table for all the foreign keys, drop them and recreate them. You'll have to use dynamic sql in your sp to do this where you can loop through the fk defn, put them in a varchar and append/edit to include CASCADE and then execute stmt.

Answer 2

make a new stored procedure, where the only parameter is the name of the table to process. In that procedure, you'll need to loop over sys.foreign_keys and sys.foreign_key_columns to build the proper drop and create syntax, just use a cursor and some prints (KISS).

call this procedure with the syntax:

EXEC sp_msforeachtable 'YourProcedureName ''?'''

and it will run for each table. Take and run the output, and you are done.

Answer 3

Here's a script I used for a similiar purpose. It does not support composite foreign keys (which use more than one field.) And it would probably need some tweaking before it will work for your situation.

select
  DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema + 
      '].[' + ForeignKeys.ForeignTableName + 
      '] DROP CONSTRAINT [' + ForeignKeys.ForeignKeyName + ']; '
,  CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema + 
      '].[' + ForeignKeys.ForeignTableName + 
      '] WITH CHECK ADD CONSTRAINT [' +  ForeignKeys.ForeignKeyName + 
      '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn + 
      ']) REFERENCES [' + schema_name(sys.objects.schema_id) + '].[' +
  sys.objects.[name] + ']([' +
  sys.columns.[name] + ']) ON DELETE CASCADE; '
 from sys.objects
  inner join sys.columns
    on (sys.columns.[object_id] = sys.objects.[object_id])
  inner join (
    select sys.foreign_keys.[name] as ForeignKeyName
     ,schema_name(sys.objects.schema_id) as ForeignTableSchema
     ,sys.objects.[name] as ForeignTableName
     ,sys.columns.[name]  as ForeignTableColumn
     ,sys.foreign_keys.referenced_object_id as referenced_object_id
     ,sys.foreign_key_columns.referenced_column_id as referenced_column_id
     from sys.foreign_keys
      inner join sys.foreign_key_columns
        on (sys.foreign_key_columns.constraint_object_id
          = sys.foreign_keys.[object_id])
      inner join sys.objects
        on (sys.objects.[object_id]
          = sys.foreign_keys.parent_object_id)
        inner join sys.columns
          on (sys.columns.[object_id]
            = sys.objects.[object_id])
           and (sys.columns.column_id
            = sys.foreign_key_columns.parent_column_id)
    ) ForeignKeys
    on (ForeignKeys.referenced_object_id = sys.objects.[object_id])
     and (ForeignKeys.referenced_column_id = sys.columns.column_id)
 where (sys.objects.[type] = 'U')
  and (sys.objects.[name] not in ('sysdiagrams'))

Answer 4

Another way, of disabling and enabling all FKs.

-- Disable the constrains
EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

-- Insert data here

-- Enable the constraints again
EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'