sql-server - puede - quitar llave foranea sql server

¿Cómo se pueden deshabilitar temporalmente las restricciones de clave externa utilizando T-SQL?

Question

¿Se admiten y desactivan las restricciones de clave externa en SQL Server? ¿O es mi única opción para drop y luego volver a create las restricciones?

Answer 1

El estándar SQL-92 permite que una constestación se declare como DEFERRABLE para que se pueda diferir (implícita o explícitamente) dentro del alcance de una transacción. Lamentablemente, a SQL Server aún le falta esta funcionalidad SQL-92.

Para mí, cambiar una restricción a NOCHECK es similar a cambiar la estructura de la base de datos sobre la marcha. Ciertamente, las restricciones que se caen son algo que debe evitarse (por ejemplo, los usuarios requieren mayores privilegios).

Answer 2

En realidad, debería poder deshabilitar las restricciones de clave externa de la misma manera que deshabilita temporalmente otras restricciones:

Alter table MyTable nocheck constraint FK_ForeignKeyConstraintName

Solo asegúrese de que está desactivando la restricción en la primera tabla que aparece en el nombre de la restricción. Por ejemplo, si mi restricción de clave externa era FK_LocationsEmployeesLocationIdEmployeeId, me gustaría usar lo siguiente:

Alter table Locations nocheck constraint FK_LocationsEmployeesLocationIdEmployeeId

aunque la violación de esta restricción producirá un error que no establece necesariamente esa tabla como la fuente del conflicto.

Answer 3

Encontrar la restricción

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id(''TABLE_NAME'')

Ejecutar el SQL generado por este SQL.

SELECT 
    ''ALTER TABLE '' +  OBJECT_SCHEMA_NAME(parent_object_id) +
    ''.['' + OBJECT_NAME(parent_object_id) + 
    ''] DROP CONSTRAINT '' + name
FROM sys.foreign_keys
WHERE referenced_object_id = object_id(''TABLE_NAME'')

Camino seguro.

Nota: Se agregó una solución para eliminar la restricción para que la tabla se pueda eliminar o modificar sin ningún error de restricción.

Answer 4

Haga clic con el botón derecho en el diseño de la tabla y vaya a Relaciones y elija la clave externa en el panel izquierdo y en el panel derecho, establezca Restricción de clave externa forzada en ''Sí'' (para habilitar restricciones de clave externa) o ''No'' (para deshabilitarlo).

Answer 5

La respuesta marcada ''905'' se ve bien pero no funciona.

Siguiendo trabajó para mí. Cualquier clave principal, clave única o restricciones predeterminadas NO SE PUEDEN inhabilitar. De hecho, si ''sp_helpconstraint'' ''muestra'' n / a ''en status_enabled - Significa que NO se puede habilitar / deshabilitar.

- Generar script para deshabilitar.

select ''ALTER TABLE '' + object_name(id) + '' NOCHECK CONSTRAINT ['' + object_name(constid) + '']''
from sys.sysconstraints 
where status & 0x4813 = 0x813 order by object_name(id)

- Generar script para HABILITAR.

select ''ALTER TABLE '' + object_name(id) + '' CHECK CONSTRAINT ['' + object_name(constid) + '']''
from sys.sysconstraints 
where status & 0x4813 = 0x813 order by object_name(id)

Answer 6

Para deshabilitar la restricción tiene ALTERAR la tabla usando NOCHECK

ALTER TABLE [TABLE_NAME] NOCHECK CONSTRAINT [ALL|CONSTRAINT_NAME]

Para habilitarlo tienes que usar CHECK doble

ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME]

• Preste atención al doble CHECK CHECK al habilitar.
• ALL significa para todas las restricciones en la tabla.

Una vez completado, si necesita verificar el estado, use este script para enumerar el estado de restricción. Será muy útil:

    SELECT (CASE 
        WHEN OBJECTPROPERTY(CONSTID, ''CNSTISDISABLED'') = 0 THEN ''ENABLED''
        ELSE ''DISABLED''
        END) AS STATUS,
        OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
        OBJECT_NAME(FKEYID) AS TABLE_NAME,
        COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
        OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
        COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
   FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO 

Answer 7

Primer comentario :)

Para el OP, la solución de kristof funcionará, a menos que haya problemas con datos masivos y problemas de globo de registro de transacciones en grandes eliminaciones. Además, incluso con el almacenamiento de tlog de sobra, ya que las eliminaciones escriben en el tlog, la operación puede llevar un tiempo MUY largo para tablas con cientos de millones de filas.

Utilizo una serie de cursores para truncar y recargar copias grandes de una de nuestras enormes bases de datos de producción con frecuencia. La solución diseñada para cuentas para múltiples esquemas, múltiples columnas de clave externa y, lo mejor de todo, se puede distribuir para su uso en SSIS.

Implica la creación de tres tablas de preparación (tablas reales) para alojar los scripts DROP, CREATE y CHECK FK, la creación e inserción de esos scripts en las tablas, y luego recorrer las tablas y ejecutarlas. La secuencia de comandos adjunta consta de cuatro partes: 1.) creación y almacenamiento de las secuencias de comandos en las tres tablas de estadificación (real), 2.) ejecución de las secuencias de comandos FK desplegadas a través de un cursor, una a una, 3.) Uso de sp_MSforeachtable para truncar todas las tablas en la base de datos que no sean nuestras tres tablas de preparación y 4.) ejecución de crear FK y verificar los scripts de FK al final de su paquete de SSIS de ETL.

Ejecute la parte de creación de script en una tarea Ejecutar SQL en SSIS. Ejecute la parte "ejecutar Drop FK Scripts" en una segunda tarea Ejecutar SQL. Coloque el script de truncamiento en una tercera tarea Ejecutar SQL, luego realice cualquier otro proceso ETL que necesite hacer antes de adjuntar los scripts CREAR y VERIFICAR en una tarea final Ejecutar SQL (o dos si lo desea) al final de su flujo de control.

El almacenamiento de los scripts en tablas reales ha demostrado ser invaluable cuando la re-aplicación de las claves foráneas falla, ya que puede seleccionar * desde sync_CreateFK, copiar / pegar en su ventana de consulta, ejecutarlas una a la vez y corregir los problemas de datos una vez que encontrar los que fallaron / todavía no se pueden volver a aplicar.

No vuelva a ejecutar la secuencia de comandos si falla sin asegurarse de volver a aplicar todas las claves / comprobaciones externas antes de hacerlo, o probablemente perderá algo de creación y verificará las secuencias de comandos fk ya que nuestras tablas de preparación se eliminan y recreados antes de la creación de los scripts a ejecutar.

----------------------------------------------------------------------------
1)
/*
Author:         Denmach
DateCreated:    2014-04-23
Purpose:        Generates SQL statements to DROP, ADD, and CHECK existing constraints for a 
                database.  Stores scripts in tables on target database for execution.  Executes
                those stored scripts via independent cursors. 
DateModified:
ModifiedBy
Comments:       This will eliminate deletes and the T-log ballooning associated with it.
*/

DECLARE @schema_name SYSNAME; 
DECLARE @table_name SYSNAME; 
DECLARE @constraint_name SYSNAME; 
DECLARE @constraint_object_id INT; 
DECLARE @referenced_object_name SYSNAME; 
DECLARE @is_disabled BIT; 
DECLARE @is_not_for_replication BIT; 
DECLARE @is_not_trusted BIT; 
DECLARE @delete_referential_action TINYINT; 
DECLARE @update_referential_action TINYINT; 
DECLARE @tsql NVARCHAR(4000); 
DECLARE @tsql2 NVARCHAR(4000); 
DECLARE @fkCol SYSNAME; 
DECLARE @pkCol SYSNAME; 
DECLARE @col1 BIT; 
DECLARE @action CHAR(6);  
DECLARE @referenced_schema_name SYSNAME;



--------------------------------Generate scripts to drop all foreign keys in a database --------------------------------

IF OBJECT_ID(''dbo.sync_dropFK'') IS NOT NULL
DROP TABLE sync_dropFK

CREATE TABLE sync_dropFK
    (
    ID INT IDENTITY (1,1) NOT NULL
    , Script NVARCHAR(4000)
    )

DECLARE FKcursor CURSOR FOR

    SELECT 
        OBJECT_SCHEMA_NAME(parent_object_id)
        , OBJECT_NAME(parent_object_id)
        , name
    FROM 
        sys.foreign_keys WITH (NOLOCK)
    ORDER BY 
        1,2;

OPEN FKcursor;

FETCH NEXT FROM FKcursor INTO 
    @schema_name
    , @table_name
    , @constraint_name

WHILE @@FETCH_STATUS = 0

BEGIN
        SET @tsql = ''ALTER TABLE ''
                + QUOTENAME(@schema_name) 
                + ''.'' 
                + QUOTENAME(@table_name)
                + '' DROP CONSTRAINT '' 
                + QUOTENAME(@constraint_name) 
                + '';'';
    --PRINT @tsql;
    INSERT sync_dropFK  (
                        Script
                        )
                        VALUES (
                                @tsql
                                )   

    FETCH NEXT FROM FKcursor INTO 
    @schema_name
    , @table_name
    , @constraint_name
    ;

END;

CLOSE FKcursor;

DEALLOCATE FKcursor;


---------------Generate scripts to create all existing foreign keys in a database --------------------------------
----------------------------------------------------------------------------------------------------------
IF OBJECT_ID(''dbo.sync_createFK'') IS NOT NULL
DROP TABLE sync_createFK

CREATE TABLE sync_createFK
    (
    ID INT IDENTITY (1,1) NOT NULL
    , Script NVARCHAR(4000)
    )

IF OBJECT_ID(''dbo.sync_createCHECK'') IS NOT NULL
DROP TABLE sync_createCHECK

CREATE TABLE sync_createCHECK
    (
    ID INT IDENTITY (1,1) NOT NULL
    , Script NVARCHAR(4000)
    )   

DECLARE FKcursor CURSOR FOR

     SELECT 
        OBJECT_SCHEMA_NAME(parent_object_id)
        , OBJECT_NAME(parent_object_id)
        , name
        , OBJECT_NAME(referenced_object_id)
        , OBJECT_ID
        , is_disabled
        , is_not_for_replication
        , is_not_trusted
        , delete_referential_action
        , update_referential_action
        , OBJECT_SCHEMA_NAME(referenced_object_id)

    FROM 
        sys.foreign_keys WITH (NOLOCK)

    ORDER BY 
        1,2;

OPEN FKcursor;

FETCH NEXT FROM FKcursor INTO 
    @schema_name
    , @table_name
    , @constraint_name
    , @referenced_object_name
    , @constraint_object_id
    , @is_disabled
    , @is_not_for_replication
    , @is_not_trusted
    , @delete_referential_action
    , @update_referential_action
    , @referenced_schema_name;

WHILE @@FETCH_STATUS = 0

BEGIN

        BEGIN
            SET @tsql = ''ALTER TABLE ''
                        + QUOTENAME(@schema_name) 
                        + ''.'' 
                        + QUOTENAME(@table_name)
                        +   CASE 
                                @is_not_trusted
                                WHEN 0 THEN '' WITH CHECK ''
                                ELSE '' WITH NOCHECK ''
                            END
                        + '' ADD CONSTRAINT '' 
                        + QUOTENAME(@constraint_name)
                        + '' FOREIGN KEY ('';

        SET @tsql2 = '''';

        DECLARE ColumnCursor CURSOR FOR 

            SELECT 
                COL_NAME(fk.parent_object_id
                , fkc.parent_column_id)
                , COL_NAME(fk.referenced_object_id
                , fkc.referenced_column_id)

            FROM 
                sys.foreign_keys fk WITH (NOLOCK)
                INNER JOIN sys.foreign_key_columns fkc WITH (NOLOCK) ON fk.[object_id] = fkc.constraint_object_id

            WHERE 
                fkc.constraint_object_id = @constraint_object_id

            ORDER BY 
                fkc.constraint_column_id;

        OPEN ColumnCursor;

        SET @col1 = 1;

        FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;

        WHILE @@FETCH_STATUS = 0

        BEGIN
            IF (@col1 = 1)
                SET @col1 = 0;
            ELSE
            BEGIN
                SET @tsql = @tsql + '','';
                SET @tsql2 = @tsql2 + '','';
            END;

            SET @tsql = @tsql + QUOTENAME(@fkCol);
            SET @tsql2 = @tsql2 + QUOTENAME(@pkCol);
            --PRINT ''@tsql = '' + @tsql 
            --PRINT ''@tsql2 = '' + @tsql2
            FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
            --PRINT ''FK Column '' + @fkCol
            --PRINT ''PK Column '' + @pkCol 
        END;

        CLOSE ColumnCursor;
        DEALLOCATE ColumnCursor;

        SET @tsql = @tsql + '' ) REFERENCES '' 
                    + QUOTENAME(@referenced_schema_name) 
                    + ''.'' 
                    + QUOTENAME(@referenced_object_name)
                    + '' ('' 
                    + @tsql2 + '')'';

        SET @tsql = @tsql
                    + '' ON UPDATE '' 
                    + 
                        CASE @update_referential_action
                            WHEN 0 THEN ''NO ACTION ''
                            WHEN 1 THEN ''CASCADE ''
                            WHEN 2 THEN ''SET NULL ''
                                ELSE ''SET DEFAULT ''
                        END

                    + '' ON DELETE '' 
                    + 
                        CASE @delete_referential_action
                            WHEN 0 THEN ''NO ACTION ''
                            WHEN 1 THEN ''CASCADE ''
                            WHEN 2 THEN ''SET NULL ''
                                ELSE ''SET DEFAULT ''
                        END

                    + 
                    CASE @is_not_for_replication
                        WHEN 1 THEN '' NOT FOR REPLICATION ''
                            ELSE ''''
                    END
                    + '';'';

        END;

    --  PRINT @tsql
        INSERT sync_createFK    
                        (
                        Script
                        )
                        VALUES (
                                @tsql
                                )

-------------------Generate CHECK CONSTRAINT scripts for a database ------------------------------
----------------------------------------------------------------------------------------------------------

        BEGIN

        SET @tsql = ''ALTER TABLE ''
                    + QUOTENAME(@schema_name) 
                    + ''.'' 
                    + QUOTENAME(@table_name)
                    + 
                        CASE @is_disabled
                            WHEN 0 THEN '' CHECK ''
                                ELSE '' NOCHECK ''
                        END
                    + ''CONSTRAINT '' 
                    + QUOTENAME(@constraint_name)
                    + '';'';
        --PRINT @tsql;
        INSERT sync_createCHECK 
                        (
                        Script
                        )
                        VALUES (
                                @tsql
                                )   
        END;

    FETCH NEXT FROM FKcursor INTO 
    @schema_name
    , @table_name
    , @constraint_name
    , @referenced_object_name
    , @constraint_object_id
    , @is_disabled
    , @is_not_for_replication
    , @is_not_trusted
    , @delete_referential_action
    , @update_referential_action
    , @referenced_schema_name;

END;

CLOSE FKcursor;

DEALLOCATE FKcursor;

--SELECT * FROM sync_DropFK
--SELECT * FROM sync_CreateFK
--SELECT * FROM sync_CreateCHECK
---------------------------------------------------------------------------
2.)
-----------------------------------------------------------------------------------------------------------------
----------------------------execute Drop FK Scripts --------------------------------------------------

DECLARE @scriptD NVARCHAR(4000)

DECLARE DropFKCursor CURSOR FOR
    SELECT Script 
    FROM sync_dropFK WITH (NOLOCK)

OPEN DropFKCursor

FETCH NEXT FROM DropFKCursor
INTO @scriptD

WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @scriptD
EXEC (@scriptD)
FETCH NEXT FROM DropFKCursor
INTO @scriptD
END
CLOSE DropFKCursor
DEALLOCATE DropFKCursor
--------------------------------------------------------------------------------
3.) 

------------------------------------------------------------------------------------------------------------------
----------------------------Truncate all tables in the database other than our staging tables --------------------
------------------------------------------------------------------------------------------------------------------


EXEC sp_MSforeachtable ''IF OBJECT_ID(''''?'''') NOT IN 
(
ISNULL(OBJECT_ID(''''dbo.sync_createCHECK''''),0),
ISNULL(OBJECT_ID(''''dbo.sync_createFK''''),0),
ISNULL(OBJECT_ID(''''dbo.sync_dropFK''''),0)
)
BEGIN TRY
 TRUNCATE TABLE ?
END TRY
BEGIN CATCH
 PRINT ''''Truncation failed on''''+ ? +''''
END CATCH;'' 
GO
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
----------------------------execute Create FK Scripts and CHECK CONSTRAINT Scripts---------------
----------------------------tack me at the end of the ETL in a SQL task-------------------------
-------------------------------------------------------------------------------------------------
DECLARE @scriptC NVARCHAR(4000)

DECLARE CreateFKCursor CURSOR FOR
    SELECT Script 
    FROM sync_createFK WITH (NOLOCK)

OPEN CreateFKCursor

FETCH NEXT FROM CreateFKCursor
INTO @scriptC

WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @scriptC
EXEC (@scriptC)
FETCH NEXT FROM CreateFKCursor
INTO @scriptC
END
CLOSE CreateFKCursor
DEALLOCATE CreateFKCursor
-------------------------------------------------------------------------------------------------
DECLARE @scriptCh NVARCHAR(4000)

DECLARE CreateCHECKCursor CURSOR FOR
    SELECT Script 
    FROM sync_createCHECK WITH (NOLOCK)

OPEN CreateCHECKCursor

FETCH NEXT FROM CreateCHECKCursor
INTO @scriptCh

WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @scriptCh
EXEC (@scriptCh)
FETCH NEXT FROM CreateCHECKCursor
INTO @scriptCh
END
CLOSE CreateCHECKCursor
DEALLOCATE CreateCHECKCursor

Answer 8

Si desea deshabilitar todas las restricciones en la base de datos, simplemente ejecute este código:

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

Para volver a encenderlos, ejecute: (la impresión es opcional, por supuesto, y solo está listando las tablas)

-- enable all constraints
exec sp_MSforeachtable @command1="print ''?''", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Me resulta útil al rellenar datos de una base de datos a otra. Es un enfoque mucho mejor que eliminar las restricciones. Como mencionó, resulta útil cuando suelta todos los datos en la base de datos y los rellena (por ejemplo, en un entorno de prueba).

Si está eliminando todos los datos, puede que esta solución le resulte útil.

También a veces es útil deshabilitar todos los desencadenantes, puede ver la solución completa here .

Answer 9

Su mejor opción es DROP y CREAR restricciones de clave externa.

No encontré ejemplos en esta publicación que funcionaran para mí "tal cual", uno no funcionaría si las claves externas hicieran referencia a esquemas diferentes, el otro no funcionaría si las claves externas hicieran referencia a varias columnas. Este script considera tanto los esquemas múltiples como las múltiples columnas por clave externa.

Aquí está la secuencia de comandos que genera las declaraciones "ADD CONSTRAINT", para varias columnas las separará por comas ( asegúrese de guardar esta salida antes de ejecutar las instrucciones DROP ):

PRINT N''-- CREATE FOREIGN KEY CONSTRAINTS --'';

SET NOCOUNT ON;
SELECT ''
PRINT N''''Creating ''+ const.const_name +''...''''
GO
ALTER TABLE '' + const.parent_obj + ''
    ADD CONSTRAINT '' + const.const_name + '' FOREIGN KEY (
            '' + const.parent_col_csv + ''
            ) REFERENCES '' + const.ref_obj + ''('' + const.ref_col_csv + '')
GO''
FROM (
    SELECT QUOTENAME(fk.NAME) AS [const_name]
        ,QUOTENAME(schParent.NAME) + ''.'' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
        ,STUFF((
                SELECT '','' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
                FROM sys.foreign_key_columns AS fcP
                WHERE fcp.constraint_object_id = fk.object_id
                FOR XML path('''')
                ), 1, 1, '''') AS [parent_col_csv]
        ,QUOTENAME(schRef.NAME) + ''.'' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
        ,STUFF((
                SELECT '','' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
                FROM sys.foreign_key_columns AS fcR
                WHERE fcR.constraint_object_id = fk.object_id
                FOR XML path('''')
                ), 1, 1, '''') AS [ref_col_csv]
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
    INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
    INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
    GROUP BY fkc.parent_object_id
        ,fkc.referenced_object_id
        ,fk.NAME
        ,fk.object_id
        ,schParent.NAME
        ,schRef.NAME
    ) AS const
ORDER BY const.const_name

Aquí está el script que genera las declaraciones de "DROP CONSTRAINT":

PRINT N''-- DROP FOREIGN KEY CONSTRAINTS --'';

SET NOCOUNT ON;

SELECT ''
PRINT N''''Dropping '' + fk.NAME + ''...''''
GO
ALTER TABLE ['' + sch.NAME + ''].['' + OBJECT_NAME(fk.parent_object_id) + '']'' + '' DROP  CONSTRAINT '' + ''['' + fk.NAME + '']
GO''
FROM sys.foreign_keys AS fk
INNER JOIN sys.schemas AS sch ON sch.schema_id = fk.schema_id
ORDER BY fk.NAME

Answer 10

Tengo una versión más útil si te interesa. Levanté un poco de código desde aquí http://www.dbaservices.com.au/news/drop-recreate-foreign-key-constraints/ modificándolo para permitir una matriz de tablas en el procedimiento almacenado y rellena la eliminación , truncar, agregar sentencias antes de ejecutarlas todas. Esto le da el control para decidir qué tablas deben truncarse.

/****** Object:  UserDefinedTableType [util].[typ_objects_for_managing]    Script Date: 03/04/2016 16:42:55 ******/
CREATE TYPE [util].[typ_objects_for_managing] AS TABLE(
    [schema] [sysname] NOT NULL,
    [object] [sysname] NOT NULL
)
GO

create procedure [util].[truncate_table_with_constraints]
@objects_for_managing util.typ_objects_for_managing readonly

--@schema sysname
--,@table sysname

as 
--select
--    @table = ''TABLE'',
--    @schema = ''SCHEMA''

declare @exec_table as table (ordinal int identity (1,1), statement nvarchar(4000), primary key (ordinal));

--print ''/*Drop Foreign Key Statements for [''+@schema+''].[''+@table+'']*/''

insert into @exec_table (statement)
select
          ''ALTER TABLE [''+SCHEMA_NAME(o.schema_id)+''].[''+ o.name+''] DROP CONSTRAINT [''+fk.name+'']''
from sys.foreign_keys fk
inner join sys.objects o
          on fk.parent_object_id = o.object_id
where 
exists ( 
select * from @objects_for_managing chk 
where 
chk.[schema] = SCHEMA_NAME(o.schema_id)  
and 
chk.[object] = o.name
) 
;
          --o.name = @table and
          --SCHEMA_NAME(o.schema_id)  = @schema

insert into @exec_table (statement) 
select
''TRUNCATE TABLE '' + src.[schema] + ''.'' + src.[object] 
from @objects_for_managing src
; 

--print ''/*Create Foreign Key Statements for [''+@schema+''].[''+@table+'']*/''
insert into @exec_table (statement)
select ''ALTER TABLE [''+SCHEMA_NAME(o.schema_id)+''].[''+o.name+''] ADD CONSTRAINT [''+fk.name+''] FOREIGN KEY ([''+c.name+'']) 
REFERENCES [''+SCHEMA_NAME(refob.schema_id)+''].[''+refob.name+'']([''+refcol.name+''])''
from sys.foreign_key_columns fkc
inner join sys.foreign_keys fk
          on fkc.constraint_object_id = fk.object_id
inner join sys.objects o
          on fk.parent_object_id = o.object_id
inner join sys.columns c
          on      fkc.parent_column_id = c.column_id and
                   o.object_id = c.object_id
inner join sys.objects refob
          on fkc.referenced_object_id = refob.object_id
inner join sys.columns refcol
          on fkc.referenced_column_id = refcol.column_id and
                   fkc.referenced_object_id = refcol.object_id
where 
exists ( 
select * from @objects_for_managing chk 
where 
chk.[schema] = SCHEMA_NAME(o.schema_id)  
and 
chk.[object] = o.name
) 
;

          --o.name = @table and
          --SCHEMA_NAME(o.schema_id)  = @schema



declare @looper int , @total_records int, @sql_exec nvarchar(4000)

select @looper = 1, @total_records = count(*) from @exec_table; 

while @looper <= @total_records 
begin

select @sql_exec = (select statement from @exec_table where ordinal =@looper)
exec sp_executesql @sql_exec 
print @sql_exec 
set @looper = @looper + 1
end

Answer 11

http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx

-- Disable all table constraints

ALTER TABLE MyTable NOCHECK CONSTRAINT ALL

-- Enable all table constraints

ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT ALL

-- Disable single constraint

ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

-- Enable single constraint

ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint

Answer 12

WITH CHECK CHECK es casi seguro requerido!

Este punto se planteó en algunas de las respuestas y comentarios, pero creo que es lo suficientemente importante como para repetirlo.

Volver a habilitar una restricción con el siguiente comando (sin WITH CHECK ) tendrá algunos inconvenientes graves .

ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint;

Con cheque | CON NOCHECK

Especifica si los datos de la tabla se validan o no en una restricción FOREIGN KEY o CHECK recién agregada o habilitada nuevamente. Si no se especifica, se asume WITH CHECK para las nuevas restricciones, y se supone WITH WITH NOCHECK para las restricciones re-habilitadas.

Si no desea verificar las nuevas restricciones CHECK o FOREIGN KEY con respecto a los datos existentes, use WITH NOCHECK. No recomendamos hacer esto, excepto en casos raros. La nueva restricción se evaluará en todas las actualizaciones de datos posteriores. Cualquier infracción de restricción que se suprima con WITH NOCHECK cuando se agregue la restricción puede hacer que las actualizaciones futuras fallen si actualizan filas con datos que no cumplen con la restricción.

El optimizador de consultas no considera las restricciones que están definidas CON NOCHECK. Dichas restricciones se ignoran hasta que se vuelvan a habilitar utilizando la tabla ALTER TABLE WITH CHECK CHECK RESTRAINT ALL.

Nota: WITH NOCHECK es el valor predeterminado para volver a habilitar restricciones. Tengo que preguntarme por qué ...

1. No se evaluarán los datos existentes en la tabla durante la ejecución de este comando; la finalización exitosa no garantiza que los datos en la tabla sean válidos de acuerdo con la restricción.
2. Durante la próxima actualización de los registros no válidos, la restricción se evaluará y fallará, lo que dará como resultado errores que pueden no estar relacionados con la actualización real realizada.
3. La lógica de la aplicación que se basa en la restricción para garantizar que los datos son válidos puede fallar.
4. El optimizador de consultas no hará uso de ninguna restricción que esté habilitada de esta manera.

La vista del sistema sys.foreign_keys proporciona cierta visibilidad del problema. Tenga en cuenta que tiene una is_disabled y una columna is_not_trusted . is_disabled indica si las futuras operaciones de manipulación de datos se validarán contra la restricción. is_not_trusted indica si todos los datos que se encuentran actualmente en la tabla se han validado con respecto a la restricción.

ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint;

¿Son tus restricciones de confianza? Descubrir...

SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1;

Answer 13

Puede deshabilitar temporalmente las restricciones en sus tablas, trabajar y luego reconstruirlas.

Aquí hay una manera fácil de hacerlo ...

Deshabilite todos los índices, incluidas las claves principales, que deshabilitarán todas las claves externas, luego vuelva a habilitar solo las claves principales para que pueda trabajar con ellas ...

DECLARE @sql AS NVARCHAR(max)=''''
select @sql = @sql +
    ''ALTER INDEX ALL ON ['' + t.[name] + ''] DISABLE;''+CHAR(13)
from  
    sys.tables t
where type=''u''

select @sql = @sql +
    ''ALTER INDEX '' + i.[name] + '' ON ['' + t.[name] + ''] REBUILD;''+CHAR(13)
from  
    sys.key_constraints i
join
    sys.tables t on i.parent_object_id=t.object_id
where
    i.type=''PK''


exec dbo.sp_executesql @sql;
go

[Haz algo, como cargar datos]

Luego vuelva a habilitar y reconstruir los índices ...

DECLARE @sql AS NVARCHAR(max)=''''
select @sql = @sql +
    ''ALTER INDEX ALL ON ['' + t.[name] + ''] REBUILD;''+CHAR(13)
from  
    sys.tables t
where type=''u''

exec dbo.sp_executesql @sql;
go

Answer 14

Una secuencia de comandos para gobernarlos a todos: esto combina comandos truncados y eliminados con sp_MSforeachtable para que pueda evitar eliminar y volver a crear restricciones. Solo especifique las tablas que deben eliminarse en lugar de truncarse y, para mi propósito, he incluido un filtro de esquema adicional para siempre. medida (probado en 2008r2)

declare @schema nvarchar(max) = ''and Schema_Id=Schema_id(''''Value'''')''
declare @deletiontables nvarchar(max) = ''(''''TableA'''',''''TableB'''')''
declare @truncateclause nvarchar(max) = @schema + '' and o.Name not in '' +  + @deletiontables;
declare @deleteclause nvarchar(max) = @schema + '' and o.Name in '' + @deletiontables;        

exec sp_MSforeachtable ''alter table ? nocheck constraint all'', @whereand=@schema
exec sp_MSforeachtable ''truncate table ?'', @whereand=@truncateclause
exec sp_MSforeachtable ''delete from ?'', @whereand=@deleteclause
exec sp_MSforeachtable ''alter table ? with check check constraint all'', @whereand=@schema

Answer 15

   --Drop and Recreate Foreign Key Constraints

SET NOCOUNT ON

DECLARE @table TABLE(
   RowId INT PRIMARY KEY IDENTITY(1, 1),
   ForeignKeyConstraintName NVARCHAR(200),
   ForeignKeyConstraintTableSchema NVARCHAR(200),
   ForeignKeyConstraintTableName NVARCHAR(200),
   ForeignKeyConstraintColumnName NVARCHAR(200),
   PrimaryKeyConstraintName NVARCHAR(200),
   PrimaryKeyConstraintTableSchema NVARCHAR(200),
   PrimaryKeyConstraintTableName NVARCHAR(200),
   PrimaryKeyConstraintColumnName NVARCHAR(200)    
)

INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT 
   U.CONSTRAINT_NAME, 
   U.TABLE_SCHEMA, 
   U.TABLE_NAME, 
   U.COLUMN_NAME 
FROM 
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
         ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
   C.CONSTRAINT_TYPE = ''FOREIGN KEY''

UPDATE @table SET
   PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM 
   @table T
      INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
         ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintTableSchema  = TABLE_SCHEMA,
   PrimaryKeyConstraintTableName  = TABLE_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
      ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

--SELECT * FROM @table

--DROP CONSTRAINT:
SELECT
   ''
   ALTER TABLE ['' + ForeignKeyConstraintTableSchema + ''].['' + ForeignKeyConstraintTableName + ''] 
   DROP CONSTRAINT '' + ForeignKeyConstraintName + ''

   GO''
FROM
   @table

--ADD CONSTRAINT:
SELECT
   ''
   ALTER TABLE ['' + ForeignKeyConstraintTableSchema + ''].['' + ForeignKeyConstraintTableName + ''] 
   ADD CONSTRAINT '' + ForeignKeyConstraintName + '' FOREIGN KEY('' + ForeignKeyConstraintColumnName + '') REFERENCES ['' + PrimaryKeyConstraintTableSchema + ''].['' + PrimaryKeyConstraintTableName + '']('' + PrimaryKeyConstraintColumnName + '')

   GO''
FROM
   @table

GO

Estoy de acuerdo contigo, Hamlin. Cuando transfiere datos utilizando SSIS o cuando desea replicar datos, parece bastante necesario deshabilitar temporalmente o eliminar las restricciones de clave foránea y luego volver a habilitarlas o recrearlas. En estos casos, la integridad referencial no es un problema, porque ya se mantiene en la base de datos de origen. Por lo tanto, puede estar seguro con respecto a este asunto.

Answer 16

SET NOCOUNT ON

DECLARE @table TABLE(
   RowId INT PRIMARY KEY IDENTITY(1, 1),
   ForeignKeyConstraintName NVARCHAR(200),
   ForeignKeyConstraintTableSchema NVARCHAR(200),
   ForeignKeyConstraintTableName NVARCHAR(200),
   ForeignKeyConstraintColumnName NVARCHAR(200),
   PrimaryKeyConstraintName NVARCHAR(200),
   PrimaryKeyConstraintTableSchema NVARCHAR(200),
   PrimaryKeyConstraintTableName NVARCHAR(200),
   PrimaryKeyConstraintColumnName NVARCHAR(200),
   UpdateRule NVARCHAR(100),
   DeleteRule NVARCHAR(100)   
)

INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT 
   U.CONSTRAINT_NAME, 
   U.TABLE_SCHEMA, 
   U.TABLE_NAME, 
   U.COLUMN_NAME
FROM 
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
         ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
   C.CONSTRAINT_TYPE = ''FOREIGN KEY''

UPDATE @table SET
   T.PrimaryKeyConstraintName = R.UNIQUE_CONSTRAINT_NAME,
   T.UpdateRule = R.UPDATE_RULE,
   T.DeleteRule = R.DELETE_RULE
FROM 
   @table T
      INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
         ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintTableSchema  = TABLE_SCHEMA,
   PrimaryKeyConstraintTableName  = TABLE_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
      ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

--SELECT * FROM @table

SELECT ''
BEGIN TRANSACTION
BEGIN TRY''

--DROP CONSTRAINT:
SELECT
   ''
 ALTER TABLE ['' + ForeignKeyConstraintTableSchema + ''].['' + ForeignKeyConstraintTableName + ''] 
 DROP CONSTRAINT '' + ForeignKeyConstraintName + ''
   ''
FROM
   @table

SELECT ''
END TRY

BEGIN CATCH
   ROLLBACK TRANSACTION
   RAISERROR(''''Operation failed.'''', 16, 1)
END CATCH

IF(@@TRANCOUNT != 0)
BEGIN
   COMMIT TRANSACTION
   RAISERROR(''''Operation completed successfully.'''', 10, 1)
END
''

--ADD CONSTRAINT:
SELECT ''
BEGIN TRANSACTION
BEGIN TRY''

SELECT
   ''
   ALTER TABLE ['' + ForeignKeyConstraintTableSchema + ''].['' + ForeignKeyConstraintTableName + ''] 
   ADD CONSTRAINT '' + ForeignKeyConstraintName + '' FOREIGN KEY('' + ForeignKeyConstraintColumnName + '') REFERENCES ['' + PrimaryKeyConstraintTableSchema + ''].['' + PrimaryKeyConstraintTableName + '']('' + PrimaryKeyConstraintColumnName + '') ON UPDATE '' + UpdateRule + '' ON DELETE '' + DeleteRule + ''
   ''
FROM
   @table

SELECT ''
END TRY

BEGIN CATCH
   ROLLBACK TRANSACTION
   RAISERROR(''''Operation failed.'''', 16, 1)
END CATCH

IF(@@TRANCOUNT != 0)
BEGIN
   COMMIT TRANSACTION
   RAISERROR(''''Operation completed successfully.'''', 10, 1)
END''

GO