sql - microsoft - CON CHECK ADD CONSTRAINT seguido de CHECK CONSTRAINT vs. ADD CONSTRAINT

check sql syntax

Question

Estoy buscando en la base de datos de ejemplo AdventureWorks para SQL Server 2008, y veo en sus scripts de creación que tienden a utilizar lo siguiente:

ALTER TABLE [Production].[ProductCostHistory] WITH CHECK ADD 
CONSTRAINT [FK_ProductCostHistory_Product_ProductID] FOREIGN KEY([ProductID])
  REFERENCES [Production].[Product] ([ProductID])
GO

seguido de inmediato por:

ALTER TABLE [Production].[ProductCostHistory] CHECK CONSTRAINT     
[FK_ProductCostHistory_Product_ProductID]
GO

Veo esto para claves externas (como aquí), restricciones únicas y restricciones CHECK regulares; DEFAULT restricciones DEFAULT utilizan el formato regular con el que estoy más familiarizado, como:

ALTER TABLE [Production].[ProductCostHistory] ADD  CONSTRAINT  
[DF_ProductCostHistory_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

¿Cuál es la diferencia, si hay alguna, entre hacerlo de la primera manera versus la segunda?

Answer 1

Además de los excelentes comentarios anteriores acerca de las restricciones de confianza:

select * from sys.foreign_keys where is_not_trusted = 1 ;
select * from sys.check_constraints where is_not_trusted = 1 ;

No se puede confiar en que una restricción que no es de confianza, como sugiere su nombre, represente con precisión el estado de los datos en la tabla en este momento. Sin embargo, se puede confiar en que se verifiquen los datos agregados y modificados en el futuro.

Además, el optimizador de consultas no tiene en cuenta las restricciones que no son de confianza.

El código para habilitar restricciones de verificación y restricciones de clave externa es bastante malo, con tres significados de la palabra "verificar".

ALTER TABLE [Production].[ProductCostHistory] 
WITH CHECK -- This means "Check the existing data in the table".
CHECK CONSTRAINT -- This means "enable the check or foreign key constraint".
[FK_ProductCostHistory_Product_ProductID] -- The name of the check or foreign key constraint, or "ALL".

Answer 2

Aquí hay un código que escribí para ayudarnos a identificar y corregir las restricciones no confiables en una BASE DE DATOS. Genera el código para arreglar cada problema.

    ;WITH Untrusted (ConstraintType, ConstraintName, ConstraintTable, ParentTable, IsDisabled, IsNotForReplication, IsNotTrusted, RowIndex) AS
(
    SELECT 
        ''Untrusted FOREIGN KEY'' AS FKType
        , fk.name AS FKName
        , OBJECT_NAME( fk.parent_object_id) AS FKTableName
        , OBJECT_NAME( fk.referenced_object_id) AS PKTableName 
        , fk.is_disabled
        , fk.is_not_for_replication
        , fk.is_not_trusted
        , ROW_NUMBER() OVER (ORDER BY OBJECT_NAME( fk.parent_object_id), OBJECT_NAME( fk.referenced_object_id), fk.name) AS RowIndex
    FROM 
        sys.foreign_keys fk 
    WHERE 
        is_ms_shipped = 0 
        AND fk.is_not_trusted = 1       

    UNION ALL

    SELECT 
        ''Untrusted CHECK'' AS KType
        , cc.name AS CKName
        , OBJECT_NAME( cc.parent_object_id) AS CKTableName
        , NULL AS ParentTable
        , cc.is_disabled
        , cc.is_not_for_replication
        , cc.is_not_trusted
        , ROW_NUMBER() OVER (ORDER BY OBJECT_NAME( cc.parent_object_id), cc.name) AS RowIndex
    FROM 
        sys.check_constraints cc 
    WHERE 
        cc.is_ms_shipped = 0
        AND cc.is_not_trusted = 1

)
SELECT 
    u.ConstraintType
    , u.ConstraintName
    , u.ConstraintTable
    , u.ParentTable
    , u.IsDisabled
    , u.IsNotForReplication
    , u.IsNotTrusted
    , u.RowIndex
    , ''RAISERROR( ''''Now CHECKing {%i of %i)--> %s ON TABLE %s'''', 0, 1'' 
        + '', '' + CAST( u.RowIndex AS VARCHAR(64))
        + '', '' + CAST( x.CommandCount AS VARCHAR(64))
        + '', '' + '''''''' + QUOTENAME( u.ConstraintName) + '''''''' 
        + '', '' + '''''''' + QUOTENAME( u.ConstraintTable) + '''''''' 
        + '') WITH NOWAIT;''
    + ''ALTER TABLE '' + QUOTENAME( u.ConstraintTable) + '' WITH CHECK CHECK CONSTRAINT '' + QUOTENAME( u.ConstraintName) + '';'' AS FIX_SQL
FROM Untrusted u
CROSS APPLY (SELECT COUNT(*) AS CommandCount FROM Untrusted WHERE ConstraintType = u.ConstraintType) x
ORDER BY ConstraintType, ConstraintTable, ParentTable;

Answer 3

La primera sintaxis es redundante: WITH CHECK es el predeterminado para nuevas restricciones y la restricción también está activada por defecto.

Esta sintaxis es generada por el SQL Management Studio al generar scripts sql. Supongo que es una especie de redundancia adicional, posiblemente para asegurar que la restricción esté habilitada incluso si se modifica el comportamiento de restricción predeterminado para una tabla.

Answer 4

Las restricciones de clave externa y verificación tienen el concepto de ser confiable o no confiable, así como de habilitarse y deshabilitarse. Vea la página MSDN para ALTER TABLE para más detalles.

WITH CHECK es el valor predeterminado para agregar nuevas claves foráneas y restricciones de verificación, WITH NOCHECK es el valor predeterminado para volver a habilitar la clave foránea deshabilitada y comprobar las restricciones. Es importante ser consciente de la diferencia.

Una vez dicho esto, cualquier declaración aparentemente redundante generada por las utilidades está simplemente ahí para la seguridad y / o la facilidad de codificación. No te preocupes por ellos

Answer 5

Para demostrar cómo funciona esto--

CREATE TABLE T1 (ID INT NOT NULL, SomeVal CHAR(1));
ALTER TABLE T1 ADD CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED (ID);

CREATE TABLE T2 (FKID INT, SomeOtherVal CHAR(2));

INSERT T1 (ID, SomeVal) SELECT 1, ''A'';
INSERT T1 (ID, SomeVal) SELECT 2, ''B'';

INSERT T2 (FKID, SomeOtherVal) SELECT 1, ''A1'';
INSERT T2 (FKID, SomeOtherVal) SELECT 1, ''A2'';
INSERT T2 (FKID, SomeOtherVal) SELECT 2, ''B1'';
INSERT T2 (FKID, SomeOtherVal) SELECT 2, ''B2'';
INSERT T2 (FKID, SomeOtherVal) SELECT 3, ''C1'';  --orphan
INSERT T2 (FKID, SomeOtherVal) SELECT 3, ''C2'';  --orphan

--Add the FK CONSTRAINT will fail because of existing orphaned records
ALTER TABLE T2 ADD CONSTRAINT FK_T2_T1 FOREIGN KEY (FKID) REFERENCES T1 (ID);   --fails

--Same as ADD above, but explicitly states the intent to CHECK the FK values before creating the CONSTRAINT
ALTER TABLE T2 WITH CHECK ADD CONSTRAINT FK_T2_T1 FOREIGN KEY (FKID) REFERENCES T1 (ID);    --fails

--Add the CONSTRAINT without checking existing values
ALTER TABLE T2 WITH NOCHECK ADD CONSTRAINT FK_T2_T1 FOREIGN KEY (FKID) REFERENCES T1 (ID);  --succeeds
ALTER TABLE T2 CHECK CONSTRAINT FK_T2_T1;   --succeeds since the CONSTRAINT is attributed as NOCHECK

--Attempt to enable CONSTRAINT fails due to orphans
ALTER TABLE T2 WITH CHECK CHECK CONSTRAINT FK_T2_T1;    --fails

--Remove orphans
DELETE FROM T2 WHERE FKID NOT IN (SELECT ID FROM T1);

--Enabling the CONSTRAINT succeeds
ALTER TABLE T2 WITH CHECK CHECK CONSTRAINT FK_T2_T1;    --succeeds; orphans removed

--Clean up
DROP TABLE T2;
DROP TABLE T1;

Answer 6

WITH CHECK es de hecho el comportamiento predeterminado, sin embargo, es una buena práctica incluirlo dentro de su codificación.

El comportamiento alternativo es, por supuesto, utilizar WITH NOCHECK , por lo que es bueno definir explícitamente sus intenciones. Esto se usa a menudo cuando está jugando con / modificando / cambiando particiones en línea.

Answer 7

WITH NOCHECK se usa también cuando uno tiene datos existentes en una tabla que no se ajustan a la restricción tal como se define y no desea que entren en conflicto con la nueva restricción que está implementando ...