ALTER TABLE table_constraint (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Warehouse no Microsoft Fabric

Especifica as propriedades de uma restrição PRIMARY KEY, UNIQUE, FOREIGN KEY ou CHECK, ou uma definição DEFAULT adicionada a uma tabela usando ALTER TABLE.

Convenções de sintaxe de Transact-SQL

Sintaxe

[ CONSTRAINT constraint_name ]   
{   
    { PRIMARY KEY | UNIQUE }   
        [ CLUSTERED | NONCLUSTERED ]   
        (column [ ASC | DESC ] [ ,...n ] )  
        [ WITH FILLFACTOR = fillfactor   
        [ WITH ( <index_option>[ , ...n ] ) ]  
        [ ON { partition_scheme_name ( partition_column_name ... )  
          | filegroup | "default" } ]   
    | FOREIGN KEY   
        ( column [ ,...n ] )  
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]   
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]   
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]   
        [ NOT FOR REPLICATION ]   
    | CONNECTION
        ( { node_table TO node_table } 
          [ , {node_table TO node_table }]
          [ , ...n ]
        )
        [ ON DELETE { NO ACTION | CASCADE } ]
    | DEFAULT constant_expression FOR column [ WITH VALUES ]   
    | CHECK [ NOT FOR REPLICATION ] ( logical_expression )  
}

Argumentos

CONSTRAINT
Especifica o início de uma definição para uma restrição PRIMARY KEY, UNIQUE, FOREIGN KEY ou CHECK, ou um DEFAULT.

constraint_name
É o nome da restrição. Os nomes de restrição devem seguir as regras para identificadores, a não ser que o nome não possa começar com uma tecla jogo da velha (#). Se constraint_name não for fornecido, um nome gerado pelo sistema será atribuído à restrição.

PRIMARY KEY
É uma restrição que impõe a integridade de entidade para uma coluna ou colunas especificadas usando um índice exclusivo. Somente uma restrição PRIMARY KEY pode ser criada para cada tabela.

UNIQUE
É uma restrição que fornece a integridade de entidade para uma coluna ou colunas especificadas usando um índice exclusivo.

CLUSTERED | NONCLUSTERED
Especifica que um índice clusterizado ou não clusterizado é criado para a restrição PRIMARY KEY ou UNIQUE. As restrições PRIMARY KEY usam como padrão CLUSTERED. As restrições UNIQUE usam como padrão NONCLUSTERED.

Se uma restrição ou índice clusterizado já existir em uma tabela, CLUSTERED não poderá ser especificado. Se uma restrição ou índice clusterizado já existir em uma tabela, as restrições PRIMARY KEY usam como padrão NONCLUSTERED.

Colunas que são dos tipos de dados ntext, text, varchar(max) , nvarchar(max) , varbinary(max) , xml ou image não podem ser especificadas como colunas de para índice.

column
É uma coluna ou lista de colunas especificadas entre parênteses que são usadas em uma nova restrição.

[ ASC | DESC ]
Especifica a ordem na qual a coluna ou colunas que participam de restrições de tabela são classificadas. O padrão é ASC.

WITH FILLFACTOR =fillfactor
Especifica o quanto o Mecanismo de Banco de Dados deve preencher cada página de índice usada para armazenar os dados de índice. Os valores de fillfactor especificados pelo usuário podem ser de 1 a 100. Se um valor não for especificado, o padrão será 0.

Importante

A documentação de WITH FILLFACTOR = fillfactor como a única opção de índice aplicável às restrições PRIMARY KEY ou UNIQUE é mantida para fins de compatibilidade com versões anteriores, mas não será documentada dessa maneira em versões futuras. Outras opções de índice podem ser especificadas na cláusula index_option de ALTER TABLE.

ON { partition_scheme_name(partition_column_name) | filegroup| "default" }
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.

Especifica o local de armazenamento do índice criado para a restrição. Se partition_scheme_name for especificado, o índice será particionado e as partições serão mapeadas para os grupos de arquivos especificados pelo partition_scheme_name. Se filegroup for especificado, o índice será criado no grupo de arquivos nomeado. Se "default" for especificado ou se ON não for especificado de modo algum, o índice será criado no mesmo grupo de arquivos que a tabela. Se ON for especificado quando um índice clusterizado for adicionado a uma restrição PRIMARY KEY ou UNIQUE, a tabela inteira será movida para o grupo de arquivos especificado quando o índice clusterizado for criado.

Nesse contexto, o padrão não é uma palavra-chave; é um identificador para o grupo de arquivos padrão e deve ser delimitado, como em "default" ou ON [default]. Se "default" for especificado, a opção QUOTED_IDENTIFIER deverá ser ON para a sessão atual. Essa é a configuração padrão.

FOREIGN KEY REFERENCES
É uma restrição que fornece integridade referencial para obter dados na coluna. Restrições FOREIGN KEY requerem que cada valor na coluna exista na coluna especificada na tabela referenciada.

referenced_table_name
É a tabela referenciada pela restrição FOREIGN KEY.

ref_column
É uma coluna ou lista de colunas entre parênteses referenciadas pela nova restrição FOREIGN KEY.

ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
Especifica a ação que acontece nas linhas da tabela alterada, se essas linhas tiverem uma relação referencial e a linha referenciada for excluída da tabela pai. O padrão é NO ACTION.

NO ACTION
O Mecanismo de Banco de Dados do SQL Server gera um erro e a ação de excluir na linha da tabela pai é revertida.

CASCADE
As linhas correspondentes serão excluídas da tabela de referência se aquela linha for excluída da tabela pai.

SET NULL
Todos os valores que compõem a chave estrangeira são definidos como NULL quando a linha correspondente na tabela pai é excluída. Para que essa restrição seja executada, as colunas de chave estrangeira devem ser anuláveis.

SET DEFAULT
Todos os valores que incluem a chave estrangeira são definidos como seus valores padrão quando a linha correspondente na tabela pai é excluída. Para que essa restrição seja executada, todas as colunas de chave estrangeira devem ter definições padrão. Se a coluna for anulável e não houver nenhum valor padrão explícito definido, NULL se tornará o valor padrão implícito para a coluna.

Não especifique CASCADE se a tabela for incluída em uma publicação de mesclagem que usa registros lógicos. Para obter mais informações sobre registros lógicos, consulte Agrupar alterações em linhas relacionadas com registros lógicos.

ON DELETE CASCADE não poderá ser definido se um disparador INSTEAD OF ON DELETE já existir na tabela que está sendo alterada.

Por exemplo, no banco de dados do AdventureWorks2022, a tabela ProductVendor tem uma relação referencial com a tabela Vendor. A chave estrangeira ProductVendor.VendorID faz referência à chave primária Vendor.VendorID.

Se uma instrução DELETE for executada em uma linha da tabela Vendor e uma ação ON DELETE CASCADE for especificada para ProductVendor.VendorID, o Mecanismo de Banco de Dados verificará se há uma ou mais linhas dependentes na tabela ProductVendor. Se existir alguma, as linhas dependentes na tabela ProductVendor serão excluídas, além da linha referenciada na tabela Vendor.

Entretanto, se NO ACTION for especificado, o Mecanismo de Banco de Dados gerará um erro e reverterá a ação de exclusão da linha Vendor quando houver pelo menos uma linha da tabela ProductVendor que a referencie.

ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
Especifica a ação que ocorre nas linhas da tabela alterada, quando essas linhas têm uma relação referencial e a linha referenciada for atualizada na tabela pai. O padrão é NO ACTION.

NO ACTION
O Mecanismo de Banco de Dados gera um erro, e a ação de atualizar na linha da tabela pai é revertida.

CASCADE
As linhas correspondentes são atualizadas na tabela de referência quando aquela linha é atualizada na tabela pai.

SET NULL
Todos os valores que compõem a chave estrangeira são definidos como NULL quando a linha correspondente na tabela pai é atualizada. Para que essa restrição seja executada, as colunas de chave estrangeira devem ser anuláveis.

SET DEFAULT
Todos os valores que compõem a chave estrangeira são definidos como seus valores padrão quando a linha correspondente na tabela pai é atualizada. Para que essa restrição seja executada, todas as colunas de chave estrangeira devem ter definições padrão. Se a coluna for anulável e não houver nenhum valor padrão explícito definido, NULL se tornará o valor padrão implícito para a coluna.

Não especifique CASCADE se a tabela for incluída em uma publicação de mesclagem que usa registros lógicos. Para obter mais informações sobre registros lógicos, consulte Agrupar alterações em linhas relacionadas com registros lógicos.

ON UPDATE CASCADE, SET NULL ou SET DEFAULT não poderá ser definido se um gatilho INSTEAD OF de ON UPDATE já existir na tabela que está sendo alterada.

Por exemplo, no banco de dados do AdventureWorks2022, a tabela ProductVendor tem uma relação referencial com a tabela Vendor. A chave estrangeira ProductVendor.VendorID faz referência à chave primária Vendor.VendorID.

Se uma instrução UPDATE for executada em uma linha da tabela Vendor e uma ação ON UPDATE CASCADE for especificada para ProductVendor.VendorID, o Mecanismo de Banco de Dados verificará se há uma ou mais linhas dependentes na tabela ProductVendor. Se existir alguma, a linha dependente na tabela ProductVendor será atualizada, assim como a linha referenciada na tabela Vendor.

Entretanto, se NO ACTION for especificado, o Mecanismo de Banco de Dados gerará um erro e reverterá a ação de atualização da linha Vendor quando houver pelo menos uma linha da tabela ProductVendor que a referencie.

NOT FOR REPLICATION
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.

Pode ser especificado para restrições FOREIGN KEY e instruções CHECK. Se essa cláusula for especificada para uma restrição, ela não será aplicada quando os agentes de replicação executarem operações insert, update ou delete.

CONNECTION Especifica o par de tabelas de nós para os quais a restrição de borda fornecida tem permissão para se conectar. ON DELETE especifica o que acontece com as linhas da tabela de borda quando os nós que estavam conectados por meio das bordas nessa tabela de borda são excluídos.

DEFAULT
Especifica o valor padrão para a coluna. Podem ser usadas definições DEFAULT para fornecer valores para uma coluna nova nas linhas existentes de dados. As definições DEFAULT não podem ser adicionadas a colunas que tenham um tipo de dados timestamp, uma propriedade IDENTITY, uma definição DEFAULT existente ou um padrão associado. Se a coluna tiver um padrão existente, o padrão deve ser descartado antes de o novo padrão ser adicionado. Se um valor padrão for especificado para uma coluna de tipo definido pelo usuário, o tipo deverá ser compatível com uma conversão implícita de constant_expression para o tipo definido pelo usuário. Para manter a compatibilidade com versões anteriores do SQL Server, um nome de restrição pode ser atribuído a um DEFAULT.

constant_expression
É um valor literal, um NULL ou uma função de sistema usados como valor de coluna padrão. Se constant_expression for usada junto com uma coluna definida para ser de um tipo definido pelo usuário do Microsoft .NET Framework, a implementação do tipo deverá dar suporte a uma conversão implícita da constant_expression no tipo definido pelo usuário.

FOR column
Especifica a coluna associada a uma definição de DEFAULT em nível de tabela.

WITH VALUES
Ao adicionar uma coluna E uma restrição DEFAULT, se a coluna permitir valores NULLS o uso de WITH VALUES definirá, para as linhas existentes, o valor da nova coluna como o valor fornecido em constant_expression DEFAULT. Se a coluna que está sendo adicionada não permitir valores NULLS, o valor da coluna sempre será definido, para as linhas existentes, como o valor fornecido na expressão constante DEFAULT. A partir do SQL Server 2012, essa pode ser uma operação de metadados adicionando colunas não nulas como uma operação online. Se isso for usado quando a coluna relacionada também não estiver sendo adicionada, não terá qualquer efeito.

CHECK
É uma restrição que impõe a integridade de domínio limitando os possíveis valores que podem ser inseridos em uma ou mais colunas.

logical_expression
É uma expressão lógica usada em uma restrição CHECK e retorna TRUE ou FALSE. logical_expression usada com restrições CHECK não pode fazer referência a outra tabela, mas pode fazer referência a outras colunas na mesma tabela para a mesma linha. A expressão não pode referenciar um tipo de dados de alias.

Comentários

Quando restrições FOREIGN KEY ou CHECK são adicionadas, todos os dados existentes são verificados quanto a violações de restrição, a menos que a opção WITH NOCHECK seja especificada. Se qualquer violação ocorrer, ALTER TABLE falhará e um erro será retornado. Quando uma nova restrição PRIMARY KEY ou UNIQUE for adicionada a uma coluna existente, os dados na coluna ou colunas deverão ser exclusivos. Se forem encontrados valores duplicados, ALTER TABLE falhará. A opção WITH NOCHECK não tem nenhum efeito quando as restrições PRIMARY KEY ou UNIQUE são adicionadas.

Cada restrição PRIMARY KEY e UNIQUE gera um índice. O número de restrições UNIQUE e PRIMARY KEY não pode fazer com que o número de índices na tabela exceda 999 índices não clusterizados e 1 índice clusterizado. Restrições de chave estrangeira não geram automaticamente um índice. Entretanto, as colunas de chave estrangeira são frequentemente usadas em critérios de junção de consultas, correspondendo as colunas na restrição de chave estrangeira de uma tabela com as colunas de chave exclusiva ou primária em outra tabela. Um índice nas colunas de chave estrangeira habilita o Mecanismo de Banco de Dados a localizar rapidamente dados relacionados na tabela de chave estrangeira.

O SQL Server 2022 (16.x) apresenta operações retomáveis para adicionar restrições de tabela para restrições de chave primária e chave exclusiva. Para obter mais informações sobre como habilitar e usar operações retomáveis ALTER TABLE ADD CONSTRAINT, confira Adição retomável de restrições de tabela.

Exemplos

Para obter exemplos, confira ALTER TABLE (Transact-SQL).

Próximas etapas