Restrições de chave primária e chave estrangeiraPrimary and Foreign Key Constraints

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Chave primárias e estrangeiras são dois tipos de restrições que podem ser usadas para impor integridade de dados nas tabelas do SQL ServerSQL Server .Primary keys and foreign keys are two types of constraints that can be used to enforce data integrity in SQL ServerSQL Server tables. Esses são objetos de banco de dados importantes.These are important database objects.

Este tópico inclui as seções a seguir.This topic contains the following sections.

Restrições PRIMARY KEYPrimary Key Constraints

Foreign Key ConstraintsForeign Key Constraints

Tarefas relacionadasRelated Tasks

Restrições PRIMARY KEYPrimary Key Constraints

Geralmente, uma tabela tem uma coluna ou uma combinação de colunas que contém valores que identificam exclusivamente cada linha na tabela.A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. Essa coluna, ou colunas, é chamada de chave primária (PK) da tabela e impõe a integridade da entidade da mesma.This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. Como as restrições PRIMARY KEY garantem dados exclusivos, elas são frequentemente definidas em uma coluna de identidade.Because primary key constraints guarantee unique data, they are frequently defined on an identity column.

Quando especificar uma restrição PRIMARY KEY para uma tabela, o Mecanismo de Banco de DadosDatabase Engine impõe a exclusividade dos dados criando automaticamente um índice exclusivo para as colunas de chave primária.When you specify a primary key constraint for a table, the Mecanismo de Banco de DadosDatabase Engine enforces data uniqueness by automatically creating a unique index for the primary key columns. Esse índice também permite um acesso rápido aos dados quando a chave primária é usada em consultas.This index also permits fast access to data when the primary key is used in queries. Se uma restrição de chave primária for definida em mais de uma coluna, os valores poderão ser duplicados em uma coluna, mas cada combinação de valores de todas as colunas na definição da restrição de chave primária deve ser exclusiva.If a primary key constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the primary key constraint definition must be unique.

Conforme mostrado na ilustração a seguir, as colunas ProductID e VendorID da tabela Purchasing.ProductVendor formam uma restrição de chave primária composta para essa tabela.As shown in the following illustration, the ProductID and VendorID columns in the Purchasing.ProductVendor table form a composite primary key constraint for this table. Isso garante que cada linha da tabela ProductVendor tem uma combinação exclusiva de ProductID e VendorID.This makes sure that every row in the ProductVendor table has a unique combination of ProductID and VendorID. Isso impede a inserção de linhas duplicadas.This prevents the insertion of duplicate rows.

Restrição PRIMARY KEY de composiçãoComposite PRIMARY KEY constraint

  • Uma tabela pode conter apenas uma restrição PRIMARY KEY.A table can contain only one primary key constraint.

  • Uma chave primária não pode exceder 16 colunas e o comprimento de chave total de 900 bytes.A primary key cannot exceed 16 columns and a total key length of 900 bytes.

  • O índice gerado por uma restrição PRIMARY KEY não pode fazer com que o número de índices da tabela exceda 999 índices não clusterizados e 1 índice clusterizado.The index generated by a primary key constraint cannot cause the number of indexes on the table to exceed 999 nonclustered indexes and 1 clustered index.

  • Se CLUSTERED ou NONCLUSTERED não estiver especificado para uma restrição PRIMARY KEY, CLUSTERED será usado se não houver índices clusterizados na tabela.If clustered or nonclustered is not specified for a primary key constraint, clustered is used if there no clustered index on the table.

  • Todas as colunas definidas em uma restrição PRIMARY KEY devem ser definidas como NOT NULL.All columns defined within a primary key constraint must be defined as not null. Se a nulidade não for especificada, todas as colunas participantes de uma restrição PRIMARY KEY deverão ter sua nulidade definida como NOT NULL.If nullability is not specified, all columns participating in a primary key constraint have their nullability set to not null.

  • Se a chave primária for definida em uma coluna de tipo CLR definida pelo usuário, a implementação do tipo deverá oferecer suporte a uma ordenação binária.If a primary key is defined on a CLR user-defined type column, the implementation of the type must support binary ordering.

Foreign Key ConstraintsForeign Key Constraints

Uma chave estrangeira (FK) é uma coluna ou combinação de colunas usada para estabelecer e impor um link entre os dados de duas tabelas, a fim de controlar os dados que podem ser armazenados na tabela de chave estrangeira.A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table. Em uma referência de chave estrangeira, cria-se um link entre duas tabelas quando a coluna ou as colunas que contêm o valor de chave primária para uma tabela são referenciadas pela coluna ou colunas de outra tabela.In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. Essa coluna torna-se uma chave estrangeira na segunda tabela.This column becomes a foreign key in the second table.

Por exemplo, a tabela Sales.SalesOrderHeader tem um link de chave estrangeira para a tabela Sales.SalesPerson porque existe uma relação lógica entre os pedidos de vendas e os vendedores.For example, the Sales.SalesOrderHeader table has a foreign key link to the Sales.SalesPerson table because there is a logical relationship between sales orders and salespeople. A coluna SalesPersonID na tabela SalesOrderHeader corresponde à coluna de chave primária da tabela SalesPerson .The SalesPersonID column in the SalesOrderHeader table matches the primary key column of the SalesPerson table. A coluna SalesPersonID na tabela SalesOrderHeader é a chave estrangeira para a tabela SalesPerson .The SalesPersonID column in the SalesOrderHeader table is the foreign key to the SalesPerson table. Criando essa relação de chave estrangeira, um valor para SalesPersonID não poderá ser inserido na tabela SalesOrderHeader se ela não existir na tabela SalesPerson .By creating this foreign key relationship, a value for SalesPersonID cannot be inserted into the SalesOrderHeader table if it does not already exist in the SalesPerson table.

Uma tabela pode fazer referência a no máximo 253 outras tabelas e colunas como chaves estrangeiras (referências de saída).A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 (13.x)SQL Server 2016 (13.x) aumenta de 253 para 10.000 o limite para o número de outras tabelas e colunas que podem fazer referência a colunas em uma única tabela (referências de entrada).increases the limit for the number of other table and columns that can reference columns in a single table (incoming references), from 253 to 10,000. (Requer, no mínimo, o nível de compatibilidade 130.) O aumento tem as seguintes restrições:(Requires at least 130 compatibility level.) The increase has the following restrictions:

  • Só há suporte para mais de 253 referências de chave estrangeira em operações DELETE DML.Greater than 253 foreign key references are only supported for DELETE DML operations. As operações UPDATE e MERGE não têm suporte.UPDATE and MERGE operations are not supported.

  • Uma tabela com uma referência de chave estrangeira a ela mesma ainda é limitada a 253 referências de chave estrangeira.A table with a foreign key reference to itself is still limited to 253 foreign key references.

  • O uso de mais de 253 referências de chave estrangeira não está disponível atualmente para índices columnstore, tabelas com otimização de memória, Stretch Database nem tabelas particionadas de chave estrangeiras.Greater than 253 foreign key references are not currently available for columnstore indexes, memory-optimized tables, Stretch Database, or partitioned foreign key tables.

Índices em restrições de chave estrangeiraIndexes on Foreign Key Constraints

Diferente das restrições de chave primária, a criação de uma restrição de chave estrangeira não cria automaticamente um índice correspondente.Unlike primary key constraints, creating a foreign key constraint does not automatically create a corresponding index. No entanto, a criação manual de um índice em uma chave estrangeira geralmente é útil pelos seguintes motivos:However, manually creating an index on a foreign key is often useful for the following reasons:

  • As colunas de chave estrangeira são frequentemente usadas em critérios de junção quando os dados de tabelas relacionadas são combinados em consultas, fazendo a correspondência de uma ou mais colunas na restrição FOREIGN KEY de uma tabela com uma ou mais colunas de chave exclusiva ou primária de outra tabela.Foreign key columns are frequently used in join criteria when the data from related tables is combined in queries by matching the column or columns in the foreign key constraint of one table with the primary or unique key column or columns in the other table. Um índice habilita o Mecanismo de Banco de DadosDatabase Engine a localizar rapidamente dados relacionados na tabela de chave estrangeira.An index enables the Mecanismo de Banco de DadosDatabase Engine to quickly find related data in the foreign key table. Porém, a criação desse índice não é obrigatória.However, creating this index is not required. Os dados de duas tabelas relacionadas podem ser combinados até mesmo se nenhuma restrição PRIMARY KEY ou FOREIGN KEY tiver sido definida entre as tabelas, mas uma relação de chave estrangeira entre duas tabelas indica que estas foram otimizadas para serem combinadas em uma consulta que usa chaves como critérios.Data from two related tables can be combined even if no primary key or foreign key constraints are defined between the tables, but a foreign key relationship between two tables indicates that the two tables have been optimized to be combined in a query that uses the keys as its criteria.

  • As alterações feitas em restrições PRIMARY KEY são verificadas com restrições FOREIGN KEY em tabelas relacionadas.Changes to primary key constraints are checked with foreign key constraints in related tables.

Integridade referencialReferential Integrity

Embora o propósito principal da restrição FOREIGN KEY seja controlar os dados que podem ser armazenados na tabela de chave estrangeira, ela também controla as alterações efetuadas nos dados da tabela de chave primária.Although the main purpose of a foreign key constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table. Por exemplo, se a linha de um vendedor for excluída da tabela Sales.SalesPerson e a ID do vendedor for usada para pedidos de vendas na tabela Sales.SalesOrderHeader , a integridade relacional entre as duas tabelas será quebrada; os pedidos de vendas do vendedor excluído ficarão órfãos na tabela SalesOrderHeader sem um link para os dados na tabela SalesPerson .For example, if the row for a salesperson is deleted from the Sales.SalesPerson table, and the salesperson's ID is used for sales orders in the Sales.SalesOrderHeader table, the relational integrity between the two tables is broken; the deleted salesperson's sales orders are orphaned in the SalesOrderHeader table without a link to the data in the SalesPerson table.

Uma restrição FOREIGN KEY impede essa situação.A foreign key constraint prevents this situation. A restrição impõe a integridade referencial ao garantir que não possam ser feitas alterações na tabela de chave primária se essas alterações invalidarem o link para os dados na tabela de chave estrangeira.The constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. Se for feita uma tentativa de exclusão da linha em uma tabela de chave primária ou alteração de um valor de chave primária, a ação apresentará falha quando o valor de chave primária excluído ou alterado corresponder a um valor na restrição FOREIGN KEY de outra tabela.If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail when the deleted or changed primary key value corresponds to a value in the foreign key constraint of another table. Para obter sucesso ao alterar ou excluir uma linha em uma restrição FOREIGN KEY, você precisa primeiro excluir os dados de chave estrangeira da tabela de chave estrangeira ou alterar os dados de chave estrangeira na tabela de chave estrangeira, o que vinculará a chave estrangeira aos diversos dados de chave primária.To successfully change or delete a row in a foreign key constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data.

Integridade referencial em cascataCascading Referential Integrity

Usando restrições de integridade referencial em cascata, é possível definir as ações que o Mecanismo de Banco de DadosDatabase Engine executa quando o usuário tenta excluir ou atualizar uma chave para a qual apontam as chaves estrangeiras existentes.By using cascading referential integrity constraints, you can define the actions that the Mecanismo de Banco de DadosDatabase Engine takes when a user tries to delete or update a key to which existing foreign keys point. As ações em cascata a seguir podem ser definidas.The following cascading actions can be defined.

NO ACTIONNO ACTION
O Mecanismo de Banco de DadosDatabase Engine gera um erro e a ação de exclusão ou atualização na linha da tabela pai é revertida.The Mecanismo de Banco de DadosDatabase Engine raises an error and the delete or update action on the row in the parent table is rolled back.

CASCADECASCADE
As linhas correspondentes são atualizadas ou excluídas na tabela de referência quando essa linha é atualizada ou excluída na tabela pai.Corresponding rows are updated or deleted in the referencing table when that row is updated or deleted in the parent table. CASCADE não poderá ser especificada se uma coluna timestamp fizer parte da chave estrangeira ou da chave referenciada.CASCADE cannot be specified if a timestamp column is part of either the foreign key or the referenced key. ON DELETE CASCADE não poderá ser especificada para a tabela que tenha um gatilho INSTEAD OF DELETE.ON DELETE CASCADE cannot be specified for a table that has an INSTEAD OF DELETE trigger. ON UPDATE CASCADE não poderá ser especificada para tabelas que tenham um gatilho INSTEAD OF UPDATE.ON UPDATE CASCADE cannot be specified for tables that have INSTEAD OF UPDATE triggers.

SET NULLSET NULL
Todos os valores que compõem a chave estrangeira são definidos como NULL quando a linha correspondente da tabela pai é atualizada ou excluída.All the values that make up the foreign key are set to NULL when the corresponding row in the parent table is updated or deleted. Para que essa restrição seja executada, as colunas de chave estrangeira devem ser anuláveis.For this constraint to execute, the foreign key columns must be nullable. Não poderá ser especificada para tabelas que tenham gatilhos INSTEAD OF UPDATE.Cannot be specified for tables that have INSTEAD OF UPDATE triggers.

SET DEFAULTSET DEFAULT
Todos os valores que compõem a chave estrangeira serão definidos com seus valores padrão se a linha correspondente na tabela pai for atualizada ou excluída.All the values that make up the foreign key are set to their default values if the corresponding row in the parent table is updated or deleted. Para que essa restrição seja executada, todas as colunas de chave estrangeira devem ter definições padrão.For this constraint to execute, all foreign key columns must have default definitions. 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.If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column. Não poderá ser especificada para tabelas que tenham gatilhos INSTEAD OF UPDATE.Cannot be specified for tables that have INSTEAD OF UPDATE triggers.

CASCADE, SET NULL, SET DEFAULT e NO ACTION podem ser combinados nas tabelas que tenham relacionamentos referenciais entre si.CASCADE, SET NULL, SET DEFAULT and NO ACTION can be combined on tables that have referential relationships with each other. Se o Mecanismo de Banco de DadosDatabase Engine encontrar NO ACTION, parará e reverterá as ações CASCATA, SET NULL e SET DEFAULT.If the Mecanismo de Banco de DadosDatabase Engine encounters NO ACTION, it stops and rolls back related CASCADE, SET NULL and SET DEFAULT actions. Quando uma instrução DELETE provoca uma combinação de ações CASCADE, SET NULL, SET DEFAULT e NO ACTION, todas as ações CASCADE, SET NULL e SET DEFAULT são aplicadas antes que o Mecanismo de Banco de DadosDatabase Engine verifique se existe alguma NO ACTION.When a DELETE statement causes a combination of CASCADE, SET NULL, SET DEFAULT and NO ACTION actions, all the CASCADE, SET NULL and SET DEFAULT actions are applied before the Mecanismo de Banco de DadosDatabase Engine checks for any NO ACTION.

Gatilhos e ações referenciais em cascataTriggers and Cascading Referential Actions

As ações referenciais em cascata acionam os gatilhos de AFTER UPDATE ou AFTER DELETE da seguinte maneira:Cascading referential actions fire the AFTER UPDATE or AFTER DELETE triggers in the following manner:

  • Todas as ações referenciais em cascata causadas diretamente por DELETE ou UPDATE originais são executadas em primeiro lugar.All the cascading referential actions directly caused by the original DELETE or UPDATE are performed first.

  • Se houver gatilhos AFTER definidos nas tabelas afetadas, esses gatilhos serão acionados depois que todas as ações referenciais em cascata forem executadas.If there are any AFTER triggers defined on the affected tables, these triggers fire after all cascading actions are performed. Os gatilhos são acionados em ordem oposta à ordem da ação em cascata.These triggers fire in opposite order of the cascading action. Se houver vários gatilhos em uma única tabela, eles serão acionados em ordem aleatória, a menos que haja um gatilho dedicado final ou inicial para a tabela.If there are multiple triggers on a single table, they fire in random order, unless there is a dedicated first or last trigger for the table. Essa ordem é especificada usando sp_settriggerorder.This order is as specified by using sp_settriggerorder.

  • Se várias cadeias em cascata se originarem da tabela que era o destino direto de uma ação UPDATE ou DELETE, a ordem em que essas cadeias acionam seus respectivos gatilhos não é especificada.If multiple cascading chains originate from the table that was the direct target of an UPDATE or DELETE action, the order in which these chains fire their respective triggers is unspecified. Porém, uma cadeia sempre aciona todos os seus gatilhos antes que outra cadeia inicie o acionamento.However, one chain always fires all its triggers before another chain starts firing.

  • Um gatilho AFTER em uma tabela que seja o destino direto de ações UPDATE ou DELETE é acionado independentemente de alguma linha ter sido ou não afetada.An AFTER trigger on the table that is the direct target of an UPDATE or DELETE action fires regardless of whether any rows are affected. Não há nenhuma outra tabela afetada em cascata nesse caso.There are no other tables affected by cascading in this case.

  • Se algum dos gatilhos anteriores executar operações UPDATE ou DELETE em outras tabelas, essas ações poderão dar início a cadeias secundárias em cascata.If any one of the previous triggers perform UPDATE or DELETE operations on other tables, these actions can start secondary cascading chains. Essas cadeias secundárias são processadas para todas as operações UPDATE ou DELETE em dado momento após o acionamento de todos os gatilhos em todas as cadeias primárias.These secondary chains are processed for each UPDATE or DELETE operation at a time after all triggers on all primary chains fire. Esse processo pode ser repetido recursivamente para operações UPDATE ou DELETE subsequentes.This process may be recursively repeated for subsequent UPDATE or DELETE operations.

  • Executar CREATE, ALTER, DELETE ou outras operações DDL (Data Definition Language) nos gatilhos pode fazer com que os gatilhos DDL sejam acionados.Performing CREATE, ALTER, DELETE, or other data definition language (DDL) operations inside the triggers may cause DDL triggers to fire. Isso pode, subsequentemente, executar operações DELETE ou UPDATE que iniciam cadeias e gatilhos adicionais em cascata.This may subsequently perform DELETE or UPDATE operations that start additional cascading chains and triggers.

  • Se um erro for gerado em qualquer cadeia de ação referencial em cascata, um erro é ativado, nenhum gatilho AFTER é acionado naquela cadeia e a operação DELETE ou UPDATE que criou a cadeia é revertida.If an error is generated inside any particular cascading referential action chain, an error is raised, no AFTER triggers are fired in that chain, and the DELETE or UPDATE operation that created the chain is rolled back.

  • Uma tabela com um gatilho INSTEAD OF não pode ter igualmente uma cláusula REFERENCES especificando uma ação em cascata.A table that has an INSTEAD OF trigger cannot also have a REFERENCES clause that specifies a cascading action. No entanto, um gatilho AFTER em uma tabela direcionada por uma ação em cascata poderá executar instruções INSERT, UPDATE ou DELETE em outra tabela ou exibição que acionem um gatilho INSTEAD OF definido naquele objeto.However, an AFTER trigger on a table targeted by a cascading action can execute an INSERT, UPDATE, or DELETE statement on another table or view that fires an INSTEAD OF trigger defined on that object.

Tarefas relacionadasRelated Tasks

A tabela a seguir lista as tarefas comuns associadas às restrições PRIMARY KEY e FOREIGN KEY.The following table lists the common tasks associated with primary key and foreign key constraints.

TarefaTask TópicoTopic
Descreve como criar uma chave primária.Describes how to create a primary key. Criar chaves primáriasCreate Primary Keys
Descreve como excluir uma chave primária.Describes how to delete a primary key. Excluir chaves primáriasDelete Primary Keys
Descreve como modificar uma chave primária.Describes how to modify a primary key. Modificar chaves primáriasModify Primary Keys
Descreve como criar relações de chave estrangeiraDescribes how to create foreign key relationships Criar relações de chaves estrangeirasCreate Foreign Key Relationships
Descreve como modificar relações de chave estrangeira.Describes how to modify foreign key relationships. Modificar relações de chave estrangeiraModify Foreign Key Relationships
Descreve como excluir relações de chave estrangeira.Describes how to delete foreign key relationships. Excluir relações de chaves estrangeirasDelete Foreign Key Relationships
Descreve como exibir propriedades de chave estrangeira.Describes how to view foreign key properties. Exibir propriedades de chave estrangeiraView Foreign Key Properties
Descreve como desabilitar restrições de chave estrangeira para replicação.Describes how to disable foreign key constraints for replication. Desabilitar restrições FOREIGN KEY para replicaçãoDisable Foreign Key Constraints for Replication
Descreve como desabilitar restrições de chave estrangeira durante uma instrução INSERT e UPDATE.Describes how to disable foreign key constraints during an INSERT or UPDATE statement. Desabilitar restrições FOREIGN KEY com instruções INSERT e UPDATEDisable Foreign Key Constraints with INSERT and UPDATE Statements