foreign - Cómo enumerar todas las claves externas con "WITH NOCHECK" en SQL Server

sql add foreign key column

Question

¿Alguien sabe una consulta para listar todas las claves foráneas en una base de datos con la descripción "WITH NOCHECK" aplicada? (eliminarlos aumentará el rendimiento y la estabilidad).

Answer 1

CON NOCHECK solo debe aplicarse a los FK temporalmente, o se vuelven inútiles para el optimizador como lo señala su artículo vinculado. De BOL:

El optimizador de consultas no considera las restricciones que se definen CON NOCHECK. Tales restricciones se ignoran hasta que se vuelvan a habilitar utilizando la tabla ALTER TABLE CHECK CONSTRAINT ALL.

Esto identificará todas sus claves externas: (trabajando en el bit WITH NOCHECK ...)

SELECT C.TABLE_CATALOG [PKTABLE_QUALIFIER], 
       C.TABLE_SCHEMA [PKTABLE_OWNER], 
       C.TABLE_NAME [PKTABLE_NAME], 
       KCU.COLUMN_NAME [PKCOLUMN_NAME], 
       C2.TABLE_CATALOG [FKTABLE_QUALIFIER], 
       C2.TABLE_SCHEMA [FKTABLE_OWNER], 
       C2.TABLE_NAME [FKTABLE_NAME], 
       KCU2.COLUMN_NAME [FKCOLUMN_NAME], 
       RC.UPDATE_RULE, 
       RC.DELETE_RULE, 
       C.CONSTRAINT_NAME [FK_NAME], 
       C2.CONSTRAINT_NAME [PK_NAME], 
       CAST(7 AS SMALLINT) [DEFERRABILITY] 
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS C 
       INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU 
         ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA 
            AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME 
       INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 
         ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
            AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 
       INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2 
         ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA 
            AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME 
       INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
         ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA 
            AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME 
            AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION 
WHERE  C.CONSTRAINT_TYPE = ''FOREIGN KEY''

Ref .

Como comentario adicional, tanto en SQL Server 2000 como en 2005, puede verificar si algún dato infringe una restricción usando:

DBCC CHECKCONSTRAINTS (table_name)

Answer 2

SELECT * FROM sys.foreign_keys AS f Where Is_Not_Trusted = 1

Answer 3

El siguiente código recupera todas las claves foráneas que están marcadas como ''WITH NOCHECK'' y luego usa una instrucción ALTER para corregirlas:

-- configure cursor on all FKs with "WITH NOCHECK"
DECLARE UntrustedForeignKeysCursor CURSOR STATIC FOR
    SELECT  f.name,
            t.name 
    FROM    sys.foreign_keys AS f
            LEFT JOIN sys.tables AS t 
                ON f.parent_object_id = t.object_id 
    Where   Is_Not_Trusted = 1
OPEN UntrustedForeignKeysCursor

-- loop through the untrusted FKs
DECLARE @FKName varchar(100)
DECLARE @TableName varchar(100)
FETCH NEXT FROM UntrustedForeignKeysCursor INTO @FKName, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

    -- Rebuild the FK constraint WITH CHECK
    EXEC (''ALTER TABLE '' + @TableName + '' WITH CHECK CHECK CONSTRAINT '' + @FKName)

    -- get next user
    FETCH NEXT FROM UntrustedForeignKeysCursor INTO @FKName, @TableName

END

-- cleanup
CLOSE UntrustedForeignKeysCursor

Answer 4

La siguiente secuencia de comandos generará las declaraciones alter que verificará los datos existentes y evitará cualquier nueva violación de las claves externas que actualmente no son de confianza (''con nocheck'').

Ejecútelo en SQL Server Management Studio para generar las secuencias de comandos y luego cópielas en una ventana de consulta para ejecutarlas.

select
    ''alter table '' + quotename(s.name) + ''.'' + quotename(t.name) + '' with check check constraint '' + fk.name +'';''
from 
    sys.foreign_keys fk
inner join
    sys.tables t
on
    fk.parent_object_id = t.object_id
inner join
    sys.schemas s
on
    t.schema_id = s.schema_id
where 
    fk.is_not_trusted = 1

Answer 5

Lo siguiente devolverá el nombre de las claves foráneas en la base de datos actual que están deshabilitadas, es decir, WITH NOCHECK

Para SQL Server 2005/2008:

select * from sys.foreign_keys where is_disabled=1

Hubo una discusión en la respuesta sobre la diferencia entre discapacitados y no confiables. Lo que está debajo explica la diferencia Aquí hay un código para aclarar la diferencia entre is_disabled y isnotrusted.

-- drop table t1
-- drop table t2
create table t1(i int not null, fk int not null)
create table t2(i int not null)
-- create primary key on t2
alter table t2
add constraint pk_1 primary key (i)
-- create foriegn key on t1
alter table t1
add constraint fk_1 foreign key (fk)
    references t2 (i)
--insert some records
insert t2 values(100)
insert t2 values(200)
insert t2 values(300)
insert t2 values(400)
insert t2 values(500)
insert t1 values(1,100)
insert t1 values(2,100)
insert t1 values(3,500)
insert t1 values(4,500)
----------------------------
-- 1. enabled and trusted
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO

-- 2. disable the constraint
alter table t1 NOCHECK CONSTRAINT fk_1
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO

-- 3. re-enable constraint, data isnt checked, so not trusted.
-- this means the optimizer will still have to check the column
alter table  t1 CHECK CONSTRAINT fk_1 
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO

--4. drop the foreign key constraint & re-add 
-- it making sure its checked
-- constraint is then enabled and trusted
alter table t1  DROP CONSTRAINT fk_1
alter table t1 WITH CHECK 
add constraint fk_1 foreign key (fk)
    references t2 (i)
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO


--5. drop the foreign key constraint & add but dont check
-- constraint is then enabled, but not trusted
alter table t1  DROP CONSTRAINT fk_1
alter table t1 WITH NOCHECK 
add constraint fk_1 foreign key (fk)
    references t2 (i)
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO

is_disabled significa que la restricción está deshabilitada

isnottrusted significa que SQL Server no confía en que la columna se haya comparado con la tabla de clave externa.

Por lo tanto, no puede suponerse que se volverá a optimizar la habilitación de la restricción de clave externa. Para asegurarse de que el optimizador confíe en la columna, lo mejor es soltar la restricción de clave externa y volver a crearla con la opción WITH CHECK (4.).

Answer 6

Sé que esta es una vieja pregunta con algunas respuestas antiguas que tienen buena información. Sin embargo, solo quería compartir un script que he estado usando para abordar esta área problemática en bastantes bases de datos diferentes para nosotros:

-- Foreign Keys
SELECT ''ALTER TABLE '' + o.name + '' WITH CHECK CHECK CONSTRAINT '' + i.name + '';'' AS AlterStatement
from sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0;
GO

-- Constraints
SELECT ''ALTER TABLE '' + o.name + '' WITH CHECK CHECK CONSTRAINT '' + i.name + '';'' AS AlterStatement
from sys.check_constraints i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0;
GO

Esto generará una colección de instrucciones ALTER para solucionar este problema de "NO CHECK" con claves externas y restricciones. Esto se basa en algunas consultas proporcionadas por Brent Ozar, pero modificadas por mí para mis propósitos y facilidad de uso. Esto podría modificarse fácilmente con UNION para convertirlo en una sola consulta.

FYI, lo he usado exclusivamente en entornos de Azure SQL Database. No estoy seguro de si existen limitaciones en las versiones anteriores de SQL Server, pero funciona muy bien en Azure.