SQL Script to alter ALL Foreign Keys to add ON DEL

问题:

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?

回答1:

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. EDIT: In particular it does not handle multi-column foreign keys correctly.

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'))

回答2:

Andomar's answer above is good but works for single-column foreign key constraints only. I adapted it a little for multi-column constraints:

create function dbo.fk_columns (@constraint_object_id int)
returns varchar(255)
as begin
declare @r varchar(255)
select @r = coalesce(@r + ',', '') + c.name
from sys.foreign_key_columns fkc
join sys.columns c
  on  fkc.parent_object_id = c.object_id
  and fkc.parent_column_id = c.column_id
where fkc.constraint_object_id = @constraint_object_id
return @r
end

select distinct
  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(' + dbo.fk_columns(constraint_object_id) + ')' +
  'REFERENCES [' + schema_name(sys.objects.schema_id) + '].[' +
  sys.objects.[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
 ,sys.foreign_keys.object_id as constraint_object_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)
-- Uncomment this if you want to include only FKs that already
-- have a cascade constraint.
--       where (delete_referential_action_desc = 'CASCADE' or update_referential_action_desc = 'CASCADE')
) 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'))

You can also use the query to help remove ON DELETE CASCADE from FKs that currently have it.

This still doesn't handle the case where the columns are named different things in the two tables - another user-defined function would need to be defined for that.

回答3:

More Standards compliant solution:

;WITH CTE AS 
(
    SELECT  
         KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
        ,KCU1.TABLE_SCHEMA AS FK_SCHEMA_NAME 
        ,KCU1.TABLE_NAME AS FK_TABLE_NAME 
        ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
        ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
        ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
        ,KCU2.TABLE_SCHEMA AS REFERENCED_SCHEMA_NAME 
        ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME 
        ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME 
        ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 
        ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
        AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
        AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
        ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
        AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
        AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
        AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION 
)


SELECT 
     FK_CONSTRAINT_NAME
    --,FK_SCHEMA_NAME
    --,FK_TABLE_NAME
    --,FK_COLUMN_NAME
    --,FK_ORDINAL_POSITION
    --,REFERENCED_CONSTRAINT_NAME
    --,REFERENCED_SCHEMA_NAME
    --,REFERENCED_TABLE_NAME
    --,REFERENCED_COLUMN_NAME
    --,REFERENCED_ORDINAL_POSITION

    ,
        'ALTER TABLE [' + FK_SCHEMA_NAME + ']' 
        + '.[' + FK_TABLE_NAME + '] ' 
        + 'DROP CONSTRAINT [' + FK_CONSTRAINT_NAME + ']; ' 
    AS DropStmt 

    ,
    'ALTER TABLE [' + FK_SCHEMA_NAME + ']' 
        + '.[' + FK_TABLE_NAME + '] ' + 
        + 'WITH CHECK ADD CONSTRAINT [' +  FK_CONSTRAINT_NAME + '] ' 
        + 'FOREIGN KEY([' + FK_COLUMN_NAME + ']) ' 
        + 'REFERENCES [' + REFERENCED_SCHEMA_NAME + '].[' + REFERENCED_TABLE_NAME + ']([' + REFERENCED_COLUMN_NAME + ']) ON DELETE CASCADE; ' 
    AS CreateStmt 

FROM CTE 

WHERE (1=1) 
/*
AND FK_TABLE_NAME IN 
(
     'T_SYS_Geschossrechte'
    ,'T_SYS_Gebaeuderechte'
    ,'T_SYS_Standortrechte'
)

AND REFERENCED_TABLE_NAME NOT LIKE 'T_AP_Ref_Mandant'
*/

ORDER BY  
     FK_TABLE_NAME
    ,FK_CONSTRAINT_NAME
    ,FK_COLUMN_NAME
    ,FK_ORDINAL_POSITION
    ,REFERENCED_CONSTRAINT_NAME
    ,REFERENCED_TABLE_NAME
    ,REFERENCED_COLUMN_NAME
    ,REFERENCED_ORDINAL_POSITION

Edit:
Extended for multi-column foreign-keys:

;WITH CTE AS 
( 
  SELECT  
         KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
        ,KCU1.TABLE_SCHEMA AS FK_SCHEMA_NAME 
        ,KCU1.TABLE_NAME AS FK_TABLE_NAME 
        ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
        ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
        ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
        ,KCU2.TABLE_SCHEMA AS REFERENCED_SCHEMA_NAME 
        ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME 
        ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME 
        ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 
        ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
        AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
        AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
        ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
        AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
        AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
        AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION 
)
SELECT 
     FK_SCHEMA_NAME
    ,FK_TABLE_NAME
    ,FK_CONSTRAINT_NAME 
    --,FK_COLUMN_NAME
    --,REFERENCED_COLUMN_NAME



    ,
    'ALTER TABLE ' + QUOTENAME(FK_SCHEMA_NAME) + '.' + QUOTENAME(FK_TABLE_NAME) + ' ' 
      + 'DROP CONSTRAINT ' + QUOTENAME(FK_CONSTRAINT_NAME) + '; ' 
    AS DropStmt 

    ,
    'ALTER TABLE ' + QUOTENAME(FK_SCHEMA_NAME) + '.' + QUOTENAME(FK_TABLE_NAME) + ' 
    ADD CONSTRAINT ' + QUOTENAME(FK_CONSTRAINT_NAME) + ' 
    FOREIGN KEY('
    + 
        SUBSTRING 
        ( 
            (
                SELECT ', ' + QUOTENAME(FK.FK_COLUMN_NAME) AS [text()] 
                FROM CTE AS FK 
                WHERE FK.FK_CONSTRAINT_NAME = CTE.FK_CONSTRAINT_NAME
                AND FK.FK_SCHEMA_NAME = CTE.FK_SCHEMA_NAME
                AND FK.FK_TABLE_NAME = CTE.FK_TABLE_NAME 
                FOR XML PATH, TYPE 
            ).value('.[1]', 'nvarchar(MAX)')
            ,3, 4000
        ) 
    + ') 
'
    + '    REFERENCES ' + QUOTENAME(REFERENCED_SCHEMA_NAME) + '.' + QUOTENAME(REFERENCED_TABLE_NAME) + '(' 
    + SUBSTRING 
      ( 
          (
              SELECT ', ' + QUOTENAME(Referenced.REFERENCED_COLUMN_NAME) AS [text()] 
              FROM CTE AS Referenced 
              WHERE Referenced.FK_CONSTRAINT_NAME = CTE.FK_CONSTRAINT_NAME
              AND Referenced.REFERENCED_SCHEMA_NAME = CTE.REFERENCED_SCHEMA_NAME
              AND Referenced.REFERENCED_TABLE_NAME = CTE.REFERENCED_TABLE_NAME 
              FOR XML PATH, TYPE 
          ).value('.[1]', 'nvarchar(MAX)')
          , 3, 4000 
      )
    + ') 
    ON DELETE CASCADE 
; ' AS CreateStmt 

FROM CTE 

GROUP BY 
     FK_SCHEMA_NAME
    ,FK_TABLE_NAME
    ,FK_CONSTRAINT_NAME 

    ,REFERENCED_SCHEMA_NAME
    ,REFERENCED_TABLE_NAME

And the far simpler version for PostGreSQL:

;WITH CTE AS 
( 
  SELECT  
         KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
        ,KCU1.TABLE_SCHEMA AS FK_SCHEMA_NAME 
        ,KCU1.TABLE_NAME AS FK_TABLE_NAME 
        ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
        ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
        ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
        ,KCU2.TABLE_SCHEMA AS REFERENCED_SCHEMA_NAME 
        ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME 
        ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME 
        ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 
        ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
        AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
        AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
        ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
        AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
        AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
        AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION 
)
SELECT 
     FK_SCHEMA_NAME
    ,FK_TABLE_NAME
    ,FK_CONSTRAINT_NAME 
    --,FK_COLUMN_NAME
    --,REFERENCED_COLUMN_NAME



    ,
    'ALTER TABLE ' || QUOTE_IDENT(FK_SCHEMA_NAME) || '.' || QUOTE_IDENT(FK_TABLE_NAME) || ' ' 
      || 'DROP CONSTRAINT ' || QUOTE_IDENT(FK_CONSTRAINT_NAME) || '; ' 
    AS DropStmt 

    ,
    'ALTER TABLE ' || QUOTE_IDENT(FK_SCHEMA_NAME) || '.' || QUOTE_IDENT(FK_TABLE_NAME) || ' 
    ADD CONSTRAINT ' || QUOTE_IDENT(FK_CONSTRAINT_NAME) || ' 
    FOREIGN KEY(' || string_agg(FK_COLUMN_NAME, ', ') || ') 
'
    || '    REFERENCES ' || QUOTE_IDENT(REFERENCED_SCHEMA_NAME) || '.' || QUOTE_IDENT(REFERENCED_TABLE_NAME) || '(' || string_agg(REFERENCED_COLUMN_NAME, ', ') || ') 
    ON DELETE CASCADE 
; ' AS CreateStmt 

FROM CTE 

GROUP BY 
     FK_SCHEMA_NAME
    ,FK_TABLE_NAME
    ,FK_CONSTRAINT_NAME 

    ,REFERENCED_SCHEMA_NAME
    ,REFERENCED_TABLE_NAME

回答4:

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.

回答5:

The answer from @Andomar worked for me but it was a bit manual - you have to run it, then copy the results and run them. I needed to use this as part of my automated test setup so needed it to run in one query automatically.

I have come up with the following which gets all the SQL to run in order to modify the foreign key constraints, then actually runs it all in one go:

    IF Object_id('tempdb..#queriesForContraints') IS NOT NULL 
      BEGIN 
          DROP TABLE #queriesForContraints
      END 

    DECLARE @ignoreTablesCommaSeparated VARCHAR(1000)

    SELECT 'ALTER TABLE [' 
           + ForeignKeys.foreigntableschema + '].[' 
           + ForeignKeys.foreigntablename 
           + '] DROP CONSTRAINT [' 
           + ForeignKeys.foreignkeyname + ']; ' 
           + '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; ' AS query
    INTO #queriesForContraints
    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' --add more comma separated table names here if required
            ) ) 

    DECLARE @queryToRun NVARCHAR(MAX)

    SELECT  @queryToRun = STUFF(
            (SELECT query + ''
             FROM #queriesForContraints
             FOR XML PATH (''))
         , 1, 0, '')

    EXEC sp_executesql @statement = @queryToRun

    IF Object_id('tempdb..#queriesForContraints') IS NOT NULL 
      BEGIN 
          DROP TABLE #queriesForContraints
      END 

回答6:

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.

回答7:

A simple solution I found is to export the database to a single file, use the search function to replace all the NO ACTION to CASCADE, drop the database and import the edited file.

Information changed on the database between the export and import will be lost.