Início / dba / Perguntas / 185883
Accepted
mathewb
Asked: 2017-09-14 09:53:59 +0800 CST

ALTER TABLE CHECK CONSTRAINT é redundante?

  • 772

Eu criei uma tabela com uma restrição de chave estrangeira nela. Quando gero um script de criação de tabela por meio do SSMS, ele cria o seguinte código:

ALTER TABLE [dbo].[MainTable]  WITH CHECK ADD  CONSTRAINT [FK_MainTable_ForeignKeyTable] FOREIGN KEY([FK_Field])
REFERENCES [dbo].[ForeignKeyTable] ([PK_Field])
GO

ALTER TABLE [dbo].[MainTable] CHECK CONSTRAINT [FK_MainTable_ForeignKeyTable]
GO

Como a primeira instrução cria a restrição WITH CHECK, isso torna a segunda instrução redundante? Se não, o que o CHECK CONSTRAINT está realizando que o WITH CHECK não?

Eu vi ALTER TABLE CHECK CONSTRAINT , mas nesse caso, o SSMS está gerando a primeira instrução WITH NOCHECK, então faz sentido ter ambas as instruções.

sql-server sql-server-2014

1 respostas

  1. Best Answer

    A primeira instrução altera a tabela para adicionar a restrição. A segunda instrução habilita ou desabilita a restrição para futuras inserções ou atualizações.

    Assim, ALTER TABLE ... CHECK CONSTRAINT ...habilita a restrição para futuras inserções/atualizações. Se você especificar ALTER TABLE ... NOCHECK CONSTRAINT ..., isso desabilitará a restrição, permitindo assim que futuras inserções e atualizações sejam bem-sucedidas, mesmo que a coluna na tabela referenciada pela chave estrangeira não contenha um valor correspondente. Você pode ver a utilidade de ter ambas as instruções presentes por meio deste exemplo:

    USE tempdb;

IF OBJECT_ID(N'dbo.b', N'U') IS NOT NULL
DROP TABLE dbo.b;
IF OBJECT_ID(N'dbo.a', N'U') IS NOT NULL
DROP TABLE dbo.a;

CREATE TABLE dbo.a
(
    a_ID int NOT NULL
        CONSTRAINT a_primary_key
        PRIMARY KEY
        CLUSTERED
    , SomeData varchar(10) NOT NULL
) ON [PRIMARY];

INSERT INTO dbo.a (a_ID, SomeData)
VALUES (1, 'aaaa')
    , (2, 'bbbb')
    , (3, 'cccc');

CREATE TABLE dbo.b
(
    b_ID int NOT NULL
        CONSTRAINT b_primary_key
        PRIMARY KEY
        CLUSTERED
    , SomeData varchar(10) NOT NULL
    , a_ID int NOT NULL
) ON [PRIMARY];

INSERT INTO dbo.b (b_ID, SomeData, a_ID)
VALUES (49, 'dddd', 1)
    , (50, 'eeee', 2)
    , (51, 'ffff', 3);

    Assim, criamos duas tabelas que não possuem um relacionamento definido. Mais tarde, decidimos que queremos projetar o relacionamento, então adicionamos uma chave estrangeira dbo.bque faz referência à coluna de chave primária em dbo.a. Queremos que as linhas existentes sejam verificadas quanto à validade, mas não nos importamos com novas linhas que podem ser adicionadas no futuro 1 . 

    ALTER TABLE dbo.b 
WITH CHECK
ADD CONSTRAINT b_a_foreign_key
FOREIGN KEY (a_ID)
REFERENCES a(a_ID);

ALTER TABLE dbo.b 
NOCHECK CONSTRAINT b_a_foreign_key;

    Tudo certo. As linhas existentes foram validadas e as novas linhas não serão validadas em relação à chave estrangeira:

    INSERT INTO dbo.b (b_ID, SomeData, a_ID)
VALUES (52, 'gggg', 4);

    O conteúdo da tabela:

    SELECT *
FROM dbo.b
    LEFT JOIN dbo.a ON b.a_ID = a.a_ID

    Resultados:

    ╔══════╦══════════╦══════╦══════╦═╗══════╦═╗═══════
║ b_ID ║ SomeData ║ a_ID ║ a_ID ║ SomeData ║
╠══════╬══════════╬══════╬══════╬════════╬════════╬════╣═══
║ 49 ║ dddd ║ 1 ║ 1 ║ aaaa ║
║ 50 ║ eeee ║ 2 ║ 2 ║ bbbb ║
║ 51 ║ ffff ║ 3 ║ 3 ║ cccc ║
║ 52 ║ gggg ║ 4 ║ NULL ║ NULL ║ <-- crazy stuff
╚══════╩══════════╩══════╩══════╩════════╩════════╩═════════

    Portanto, como o código que cria scripts de tabelas no SSMS deve cobrir todas as eventualidades, ele gera um ALTER TABLE ... CHECK CONSTRAINT ...mesmo que não seja necessário e, na grande maioria dos casos, certamente não é necessário.

    1 - por que alguém iria querer que as linhas existentes fossem validadas, mas não se importar com as novas linhas é um pouco misterioso, mas aí está.

    • 4

relate perguntas