studio - sql server relationships between tables

Script SQL para alterar TODAS las claves foráneas para agregar ON DELETE CASCADE

Question

Tengo una base de datos SQL 2005 con aproximadamente 250 tablas.

Quiero habilitar temporalmente ON DELETE CASCADE a todas las Foreign Keys para que pueda hacer una eliminación masiva fácilmente.

Luego quiero desactivar ON DELETE CASCADE en todas las Foreign Keys.

La única manera que conozco de hacer esto es usar Management Studio para generar un script de creación de bases de datos completo, hacer algún tipo de búsqueda y reemplazo para quitar todo excepto Foreign Keys, guardar el script, luego hacer más búsquedas y reemplazar para agregar el ON DELETE CASCADE.

Luego ejecuto el script, hago mi eliminación y luego ejecuto el otro script.

¿Hay alguna manera más fácil de producir este script? Este método parece demasiado propenso a errores y tendré que mantener el script actualizado con cualquier otro cambio que realicemos en la base de datos, o volver a generarlo manualmente cada vez que necesite usarlo.

¿Es una opción alternativa ejecutar una selección en las tablas del sistema para "generar" el script para mí? ¿Podría incluso ser posible ejecutar una actualización en una tabla del sistema que habilita y deshabilita ON DELETE CASCADE?

Answer 1

Aquí hay un script que utilicé para un propósito similar. No es compatible con claves externas compuestas (que usan más de un campo). Y probablemente necesite algunos ajustes antes de que funcione para su situación. EDITAR: en particular, no maneja correctamente las claves externas de varias columnas.

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 2

La respuesta anterior de Andomar es buena, pero funciona solo para restricciones de clave externa de columna única. Lo adapté un poco para las restricciones de múltiples columnas:

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

También puede usar la consulta para ayudar a eliminar ON DELETE CASCADE de los FK que actualmente lo tienen.

Todavía no se maneja el caso en el que las columnas reciben nombres diferentes en las dos tablas, otra función definida por el usuario tendría que definirse para eso.

Answer 3

La respuesta de @Andomar funcionó para mí, pero fue un poco manual: tienes que ejecutarla, luego copiar los resultados y ejecutarlos. Necesitaba usar esto como parte de la configuración de prueba automática, así que necesitaba ejecutarla en una consulta automáticamente.

He encontrado lo siguiente que hace que todo el SQL se ejecute para modificar las restricciones de clave externa, y luego lo ejecuta todo de una vez:

    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 

Answer 4

Más solución conforme a los estándares:

;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

Editar:
Extendido para llaves foráneas de múltiples columnas:

;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

Y la versión mucho más simple para 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

Answer 5

Tendrás que modificar la tabla, eliminar las restricciones de FK y volver a crearlas:

Esta es la sintaxis db2. SQLServer debería ser 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;

Puede escribir su propia sp para consultar en la tabla del sistema todas las claves foráneas, soltarlas y volver a crearlas. Tendrá que usar sql dinámico en su sp para hacer esto, donde puede recorrer el defn fk, ponerlos en varchar y anexar / editar para incluir CASCADE y luego ejecutar stmt.

Answer 6

Una solución simple que encontré es exportar la base de datos a un solo archivo, usar la función de búsqueda para reemplazar todas las NO ACTION a CASCADE, soltar la base de datos e importar el archivo editado.

La información cambiada en la base de datos entre la exportación y la importación se perderá.

Answer 7

crea un nuevo procedimiento almacenado, donde el único parámetro es el nombre de la tabla a procesar. En ese procedimiento, deberá pasar por encima de sys.foreign_keys y sys.foreign_key_columns para generar la caída correcta y crear la sintaxis, simplemente use un cursor y algunas copias (KISS).

llama a este procedimiento con la sintaxis:

EXEC sp_msforeachtable ''YourProcedureName ''''?''''''

y se ejecutará para cada mesa. Toma y ejecuta la salida, y listo.