Partilhar via

Restrições de chave primária e estrangeira

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada SQL do Azurebanco de dados SQL no Microsoft Fabric

Chaves primárias e chaves estrangeiras são dois tipos de restrições que podem ser usadas para impor a integridade dos dados em tabelas do SQL Server. Estes são objetos de banco de dados importantes.

Restrições de chave primária

Uma tabela normalmente tem uma coluna ou combinação de colunas que contêm valores que identificam exclusivamente cada linha da tabela. Esta coluna, ou colunas, é chamada de chave primária (PK) da tabela e assegura a integridade da entidade da tabela. Como as restrições de chave primária garantem dados exclusivos, elas são frequentemente definidas em uma coluna de identidade.

Quando você especifica uma restrição de chave primária para uma tabela, o Mecanismo de Banco de Dados impõe a exclusividade de dados criando automaticamente um índice exclusivo para as colunas de chave primária. Esse índice também permite acesso rápido aos dados quando a chave primária é usada em consultas. Se uma restrição de chave primária for definida em mais de uma coluna, os valores poderão ser duplicados dentro de uma coluna, mas cada combinação de valores de todas as colunas na definição de restrição de chave primária deverá ser exclusiva.

Conforme mostrado na ilustração a seguir, as colunas ProductID e VendorID na tabela Purchasing.ProductVendor formam uma restrição de chave primária composta para esta tabela. Isso garante que cada linha na tabela ProductVendor tenha uma combinação exclusiva de ProductID e VendorID. Isso impede a inserção de linhas duplicadas.

Diagrama de linhas numa tabela para uma restrição de CHAVE PRIMÁRIA composta.

  • Uma tabela pode conter apenas uma restrição de chave primária.
  • Uma chave primária não pode exceder 16 colunas e um comprimento total de chave de 900 bytes.
  • O índice gerado por uma restrição de chave primária não pode fazer com que o número de índices na tabela exceda 999 índices não clusterizados e 1 índice clusterizado.
  • Se clustered ou nonclustered não for especificado para uma restrição de chave primária, clustered será usado se não houver nenhum índice clusterizado na tabela.
  • Todas as colunas definidas dentro de uma restrição de chave primária devem ser definidas como não nulas. Se a anulabilidade não for especificada, todas as colunas que participam de uma restrição de chave primária terão sua anulabilidade definida como não nula.
  • Se uma chave primária for definida em uma coluna de tipo definido pelo usuário CLR, a implementação do tipo deverá suportar ordenação binária.

Restrições de chave estrangeira

Uma chave estrangeira (FK) é uma coluna ou combinação de colunas que é usada para estabelecer e impor um link entre os dados em duas tabelas para controlar os dados que podem ser armazenados na tabela de chave estrangeira. Em uma referência de chave estrangeira, um link é criado entre duas tabelas quando a coluna ou colunas que contêm o valor da chave primária de uma tabela são referenciadas pela coluna ou colunas em outra tabela. Esta coluna torna-se uma chave estrangeira na segunda tabela.

Por exemplo, a tabela Sales.SalesOrderHeader tem um link de chave estrangeira para a tabela Sales.SalesPerson porque há uma relação lógica entre ordens de venda e vendedores. A coluna SalesPersonID na tabela SalesOrderHeader corresponde à coluna de chave primária da tabela SalesPerson. A coluna SalesPersonID na tabela SalesOrderHeader é a chave estrangeira para a tabela SalesPerson. Ao criar essa relação de chave estrangeira, um valor para SalesPersonID não pode ser inserido na tabela SalesOrderHeader se ainda não existir na tabela SalesPerson.

Uma tabela pode fazer referência a um máximo de 253 outras tabelas e colunas como chaves estrangeiras (referências de saída). O SQL Server 2016 (13.x) aumenta 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), de 253 para 10.000. (Requer pelo menos 130 níveis de compatibilidade.) O aumento tem as seguintes restrições:

  • Mais de 253 referências de chave estrangeira são suportadas apenas para operações DELETE DML. As operações UPDATE e MERGE não são suportadas.

  • Uma tabela com uma referência de chave estrangeira para si mesma ainda está limitada a 253 referências de chave estrangeira.

  • Mais de 253 referências de chave estrangeira não estão atualmente disponíveis para índices columnstore, tabelas com otimização de memória, Stretch Database ou tabelas de chave estrangeira particionadas.

    Importante

    O Stretch Database foi preterido no SQL Server 2022 (16.x) e no Banco de Dados SQL do Azure. Esse recurso será removido em uma versão futura do Mecanismo de Banco de Dados. Evite usar esse recurso em novos trabalhos de desenvolvimento e planeje modificar aplicativos que atualmente usam esse recurso.

Índices sobre restrições de chave estrangeira

Ao contrário das restrições de chave primária, a criação de uma restrição de chave estrangeira não cria automaticamente um índice correspondente. No entanto, a criação manual de um índice em uma chave estrangeira geralmente é útil pelos seguintes motivos:

  • As colunas de chave estrangeira são frequentemente usadas em critérios de junção em consultas, quando é necessário combinar dados de tabelas relacionadas, correspondendo a coluna ou colunas da restrição de chave estrangeira de uma tabela com a coluna ou colunas de chave primária ou única na outra tabela. Um índice permite que o Mecanismo de Banco de Dados encontre rapidamente dados relacionados na tabela de chaves estrangeiras. No entanto, a criação desse índice não é necessária. Os dados de duas tabelas relacionadas podem ser combinados mesmo que nenhuma chave primária ou restrições de chave estrangeira sejam definidas entre as tabelas, mas uma relação de chave estrangeira entre duas tabelas indica que as duas tabelas foram otimizadas para serem combinadas em uma consulta que usa as chaves como critério.

  • As alterações nas restrições de chave primária são verificadas com restrições de chave estrangeira em tabelas relacionadas.

Integridade referencial

Embora o principal objetivo de uma restrição de chave estrangeira seja controlar os dados que podem ser armazenados na tabela de chave estrangeira, ela também controla as alterações nos dados na tabela de chave primária. Por exemplo, se a linha de um vendedor for excluída da tabela Sales.SalesPerson e a ID do vendedor for usada para ordens de venda na tabela Sales.SalesOrderHeader, a integridade relacional entre as duas tabelas será quebrada; As ordens de venda do vendedor excluído ficam órfãs na tabela SalesOrderHeader sem um link para os dados na tabela SalesPerson.

Uma restrição de chave estrangeira evita essa situação. A restrição impõe a integridade referencial, garantindo que as alterações não possam ser feitas nos dados na tabela de chave primária se essas alterações invalidarem o link para os dados na tabela de chave estrangeira. Se for feita uma tentativa de excluir a linha em uma tabela de chave primária ou alterar um valor de chave primária, a ação falhará quando o valor da chave primária excluída ou alterada corresponder a um valor na restrição de chave estrangeira de outra tabela. Para alterar ou excluir com êxito uma linha em uma restrição de chave estrangeira, você deve primeiro excluir os dados de chave estrangeira na tabela de chave estrangeira ou alterar os dados de chave estrangeira na tabela de chave estrangeira, que vincula a chave estrangeira a diferentes dados de chave primária.

Integridade referencial em cascata

Usando restrições de integridade referencial em cascata, você pode definir as ações que o Mecanismo de Banco de Dados executa quando um usuário tenta excluir ou atualizar uma chave para a qual as chaves estrangeiras existentes apontam. As seguintes ações em cascata podem ser definidas.

  • NO ACTION

    O Mecanismo de Banco de Dados gera um erro e a ação de exclusão ou atualização na linha da tabela pai é revertida.

  • CASCADE

    As linhas correspondentes são atualizadas ou excluídas na tabela de referência quando essa linha é atualizada ou excluída na tabela pai. CASCADE não pode ser especificado se uma coluna de carimbo de data/hora fizer parte da chave estrangeira ou da chave referenciada. ON DELETE CASCADE não pode ser especificado para uma tabela que tenha um gatilho INSTEAD OF DELETE. ON UPDATE CASCADE não pode ser especificado para tabelas que tenham INSTEAD OF UPDATE gatilhos.

  • SET NULL

    Todos os valores que compõem a chave estrangeira são definidos como NULL quando a linha correspondente na tabela pai é atualizada ou excluída. Para que esta restrição seja executada, as colunas de chave estrangeira devem ser nulas. Não pode ser especificado para tabelas que possuam gatilhos INSTEAD OF UPDATE.

  • SET DEFAULT

    Todos os valores que compõem a chave estrangeira são definidos como seus valores padrão se a linha correspondente na tabela pai for atualizada ou excluída. Para que essa restrição seja executada, todas as colunas de chave estrangeira devem ter definições padrão. Se uma coluna for anulável e não houver nenhum valor padrão explícito definido, NULL se tornará o valor padrão implícito da coluna. Não pode ser especificado para tabelas que têm triggers INSTEAD OF UPDATE.

CASCADE, SET NULL, SET DEFAULTe NO ACTION podem ser combinados em tabelas que têm relações referenciais entre si. Se o Mecanismo de Banco de Dados encontrar NO ACTION, ele interromperá e reverterá as ações relacionadas CASCADE, SET NULLe SET DEFAULT. Quando uma instrução DELETE causa uma combinação de ações CASCADE, SET NULL, SET DEFAULTou NO ACTION, todas as ações CASCADE, SET NULLe SET DEFAULT são aplicadas antes que o Mecanismo de Banco de Dados verifique se há NO ACTION.

Gatilhos e ações referenciais em cascata

Ações referenciais em cascata disparam os gatilhos de AFTER UPDATE ou AFTER DELETE da seguinte maneira:

  • Todas as ações referenciais em cascata diretamente causadas pelo DELETE ou UPDATE original são realizadas primeiro.

  • Se houver algum gatilho AFTER definido nas tabelas afetadas, esses gatilhos serão acionados depois que todas as ações em cascata forem executadas. Estes gatilhos disparam na ordem inversa da ação em cascata. Se houver vários gatilhos em uma única tabela, eles serão acionados em ordem aleatória, a menos que haja um primeiro ou último gatilho dedicado para a tabela. Esta ordem é a especificada usando sp_settriggerorder.

  • Se várias cadeias em cascata tiverem origem na tabela que foi o alvo direto de uma ação UPDATE ou DELETE, a ordem em que essas cadeias disparam os seus respetivos gatilhos não é especificada. No entanto, uma cadeia sempre dispara todos os seus gatilhos antes que outra cadeia comece a disparar.

  • Um gatilho de AFTER na tabela, que é o alvo direto de uma ação UPDATE ou DELETE, é acionado independentemente de serem afetadas ou não quaisquer linhas. Neste caso, não existem outras tabelas afetadas pela cascata.

  • Se qualquer um dos gatilhos anteriores executar operações UPDATE ou DELETE em outras tabelas, essas ações poderão iniciar cadeias secundárias em cascata. Essas cadeias secundárias são processadas para cada operação UPDATE ou DELETE de cada vez, depois que todos os gatilhos em todas as cadeias primárias disparam. Este processo pode ser repetido recursivamente para operações UPDATE ou DELETE subsequentes.

  • Executar CREATE, ALTER, DELETEou outras operações de linguagem de definição de dados (DDL) dentro dos gatilhos pode fazer com que os gatilhos DDL sejam acionados. Isso pode executar subsequentemente operações DELETE ou UPDATE que iniciam cadeias em cascata e gatilhos adicionais.

  • Se um erro é gerado dentro de qualquer cadeia de ação referencial em cascata específica, um erro é gerado, nenhum gatilho AFTER é acionado nessa cadeia e a operação DELETE ou UPDATE que criou a cadeia é revertida.

  • Uma tabela que tenha um gatilho de INSTEAD OF também não pode ter uma cláusula REFERENCES que especifique uma ação em cascata. No entanto, um gatilho de AFTER em uma tabela direcionada por uma ação em cascata pode executar uma instrução INSERT, UPDATEou DELETE em outra tabela ou exibição que dispara um gatilho de INSTEAD OF definido nesse objeto.

Conteúdo relacionado