sql-server - tipos - set foreign_key_checks=0 sql server

Deshabilitando la restricción de clave externa, ¿aún no puede truncar la tabla?(SQL Server 2005)

Question

Tengo una tabla llamada PX_Child que tiene una clave externa en PX_Parent. Me gustaría deshabilitar temporalmente esta restricción FK para poder truncar PX_Parent. Sin embargo, no estoy seguro de cómo va esto.

He probado estos comandos

ALTER TABLE PX_Child NOCHECK CONSTRAINT ALL

ALTER TABLE PX_Parent NOCHECK CONSTRAINT ALL

(truncate commands)

ALTER TABLE PX_Child CHECK CONSTRAINT ALL

ALTER TABLE PX_Parent CHECK CONSTRAINT ALL

Pero el truncado aún me dice que no puede truncar PX_Parent debido a una restricción de clave externa. He buscado en toda la red y parece que no puedo encontrar lo que estoy haciendo mal, perdón por la naturaleza básica de esta pregunta.

Answer 1

El servidor SQL no le permitirá truncar la tabla mientras exista la restricción, incluso si está desactivada. Elimine la restricción y vuelva a crearla después de truncar la tabla. O simplemente suelte y vuelva a crear las tablas, lo que sea más fácil de hacer en su aplicación.

Answer 2

Hay una manera más fácil. Me enfrenté con el mismo problema y encontré esta solución: https://www.mssqltips.com/sqlservertip/3347/drop-and-recreate-all-foreign-key-constraints-in-sql-server/

Si solo ejecuta esta consulta en su base de datos, generará el T-SQL que necesita incluir antes / después de su sproc, para eliminar y luego restaurar cualquier restricción de clave externa.

No te preocupes por tratar de entender esta consulta en sí misma.

CREATE TABLE #x -- feel free to use a permanent table
(
  drop_script NVARCHAR(MAX),
  create_script NVARCHAR(MAX)
);

DECLARE @drop   NVARCHAR(MAX) = N'''',
        @create NVARCHAR(MAX) = N'''';

-- drop is easy, just build a simple concatenated list from sys.foreign_keys:
SELECT @drop += N''
ALTER TABLE '' + QUOTENAME(cs.name) + ''.'' + QUOTENAME(ct.name) 
    + '' DROP CONSTRAINT '' + QUOTENAME(fk.name) + '';''
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id];

INSERT #x(drop_script) SELECT @drop;

-- create is a little more complex. We need to generate the list of 
-- columns on both sides of the constraint, even though in most cases
-- there is only one column.
SELECT @create += N''
ALTER TABLE '' 
   + QUOTENAME(cs.name) + ''.'' + QUOTENAME(ct.name) 
   + '' ADD CONSTRAINT '' + QUOTENAME(fk.name) 
   + '' FOREIGN KEY ('' + STUFF((SELECT '','' + QUOTENAME(c.name)
   -- get all the columns in the constraint table
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.parent_column_id = c.column_id
    AND fkc.parent_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''''), TYPE).value(N''.[1]'', N''nvarchar(max)''), 1, 1, N'''')
  + '') REFERENCES '' + QUOTENAME(rs.name) + ''.'' + QUOTENAME(rt.name)
  + ''('' + STUFF((SELECT '','' + QUOTENAME(c.name)
   -- get all the referenced columns
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.referenced_column_id = c.column_id
    AND fkc.referenced_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''''), TYPE).value(N''.[1]'', N''nvarchar(max)''), 1, 1, N'''') + '');''
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
  ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs 
  ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;

UPDATE #x SET create_script = @create;

PRINT @drop;
PRINT @create;

/*
EXEC sp_executesql @drop
-- clear out data etc. here
EXEC sp_executesql @create;
*/

Genera un montón de:

ALTER TABLE [dbo].[Whatever] DROP CONSTRAINT....
--
ALTER TABLE [dbo].[Whatever] ADD CONSTRAINT....

Answer 3

No puede truncar la tabla si hay alguna clave externa que haga referencia a ella, incluidas las restricciones deshabilitadas. Debe eliminar las restricciones de clave externa o usar el comando DELETE .