Contenções de Chaves Primárias e Estrangeiras

• 07/25/2017
• 9 minutos para ler
• ul>
• s
• M
• M
• c
• J
• +1

/li>

P>Aplica a: SQL Server 2016 (13.x) e mais tarde Azure SQL Database Azure SQL Managed Instance

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

Este tópico contém as seguintes secções.

Condições de chaves primárias

Condições de chaves estrangeiras

Condições de chaves estrangeiras

Tarefas relacionadas

Condições de chaves primárias

Uma tabela tem tipicamente uma coluna ou combinação de colunas que contêm valores que identificam de forma única cada linha da tabela. Esta coluna, ou colunas, é chamada a chave primária (PK) da tabela e reforça a integridade da entidade da tabela. Como as restrições da chave primária garantem dados únicos, são frequentemente definidas numa coluna de identidade.

Quando se especifica uma restrição da chave primária para uma tabela, o Motor de Base de Dados reforça a unicidade dos dados criando automaticamente um índice único para as colunas da chave primária. Este índice também permite o acesso rápido aos dados quando a chave primária é utilizada em consultas. Se uma restrição da chave primária for definida em mais de uma coluna, os valores podem ser duplicados dentro de uma coluna, mas cada combinação de valores de todas as colunas na definição da restrição da chave primária deve ser única.

Como mostrado na ilustração seguinte, as colunas ProductID e VendorID na tabela Compras.ProductVendor formam uma restrição da chave primária composta para esta tabela. Isto assegura que cada linha na tabela ProductVendor tem uma combinação única de ProductID e VendorID. Isto impede a inserção de linhas duplicadas.

• p>p>Uma tabela pode conter apenas um constrangimento 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 agrupados e 1 índice agrupado.

• p> Se o índice agrupado ou não agrupado não for especificado para uma restrição de chave primária, o índice agrupado é utilizado se não houver nenhum índice agrupado na tabela.
• p> Todas as colunas definidas dentro de uma restrição de chave primária devem ser definidas como não nulas. Se a nulidade não for especificada, todas as colunas que participam numa restrição de chave primária têm a sua nulidade definida como não nula.

• Se uma chave primária for definida numa coluna do tipo definida pelo utilizador CLR, a implementação do tipo deve suportar a ordenação binária.

Restrições de chave estrangeira

Uma chave estrangeira (FK) é uma coluna ou combinação de colunas que é utilizada para estabelecer e impor uma ligação entre os dados em duas tabelas para controlar os dados que podem ser armazenados na tabela de chave estrangeira. Numa referência de chave estrangeira, é criada uma ligação 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 noutra tabela. Esta coluna torna-se uma chave estrangeira na segunda tabela.

Por exemplo, a tabela Sales.SalesOrderHeader tem uma ligação de chave estrangeira à tabela Sales.SalesPerson porque existe uma relação lógica entre as ordens de venda e os vendedores. A coluna SalesPersonID na tabela SalesOrderHeader corresponde à coluna chave primária da tabela SalesPerson. A coluna SalesPersonID na tabela SalesOrderHeader é a chave estrangeira para a tabela SalesPerson. Ao criar esta 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 referir um máximo de 253 outras tabelas e colunas como chaves estrangeiras (referências de saída). SQL Server 2016 (13.x) aumenta o limite para o número de outras tabelas e colunas que podem referenciar colunas numa única tabela (referências de entrada), de 253 para 10.000. (Requer pelo menos 130 nível de compatibilidade.) O aumento tem as seguintes restrições:

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

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

• p>Acima de 253 referências de chave estrangeira não estão actualmente disponíveis para índices de lojas de colunas, tabelas optimizadas para memória, base de dados Stretch, ou tabelas de chave estrangeira particionadas.

Indexes on Foreign Key Constraints

Condições de chave primária não semelhantes, criando uma restrição de chave estrangeira não cria automaticamente um índice correspondente. Contudo, a criação manual de um índice sobre uma chave estrangeira é frequentemente útil pelas seguintes razões:

• p>colunas de chave estrangeira são frequentemente utilizadas em critérios de associação quando os dados de tabelas relacionadas são combinados em consultas, fazendo corresponder a coluna ou colunas na 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 ao Motor de Base de Dados encontrar rapidamente os dados relacionados na tabela de chave estrangeira. No entanto, a criação deste índice não é necessária. Os dados de duas tabelas relacionadas podem ser combinados mesmo que nenhuma restrição de chave primária ou de chave estrangeira seja definida entre as tabelas, mas uma relação de chave estrangeira entre duas tabelas indica que as duas tabelas foram optimizadas para serem combinadas numa consulta que utiliza as chaves como critério.
• p>As alterações às restrições de chave primária são verificadas com restrições de chave estrangeira nas tabelas relacionadas.

Integridade Referencial

Embora o objectivo principal de uma restrição de chave estrangeira seja controlar os dados que podem ser armazenados na tabela de chave estrangeira, também controla as alterações aos dados na tabela de chave primária. Por exemplo, se a linha para um vendedor for eliminada da tabela Sales.SalesPerson, e o ID do vendedor for utilizado para pedidos de venda na tabela SalesOrderHeader, a integridade relacional entre as duas tabelas é quebrada; os pedidos de venda do vendedor eliminados são órfãos na tabela SalesOrderHeader sem uma ligação aos dados na tabela SalesPerson.

Uma restrição de chave estrangeira impede esta situação. A restrição reforça a integridade referencial, garantindo que não podem ser feitas alterações aos dados da tabela de chave primária se essas alterações invalidarem a ligação aos dados da tabela de chave estrangeira. Se for feita uma tentativa de apagar a linha numa tabela de chave primária ou de alterar um valor de chave primária, a acção falhará quando o valor da chave primária apagado ou alterado corresponder a um valor na restrição de chave estrangeira de outra tabela. Para alterar ou apagar com sucesso uma linha numa restrição de chave estrangeira, é necessário primeiro apagar os dados da chave estrangeira na tabela de chave estrangeira ou alterar os dados da chave estrangeira na tabela de chave estrangeira, que liga a chave estrangeira a diferentes dados de chave primária.

Cascading Referential Integrity

Utilizando restrições de integridade referencial em cascata, é possível definir as acções que o Motor de Base de Dados toma quando um utilizador tenta apagar ou actualizar uma chave para a qual apontam chaves estrangeiras existentes. As seguintes acções em cascata podem ser definidas.

NO ACTION
O Motor de Base de Dados levanta um erro e a acção de apagar ou actualizar na linha da tabela pai é retrocedida.

CASCADE
Linhas correspondentes são actualizadas ou apagadas na tabela de referência quando essa linha é actualizada ou apagada na tabela pai. CASCADE não pode ser especificado se uma coluna de carimbo temporal fizer parte quer da chave estrangeira quer 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 gatilhos INSTEAD OF UPDATE.

SET NULL
Todos os valores que compõem a chave estrangeira são definidos para NULL quando a linha correspondente na tabela pai é actualizada ou apagada. Para que esta restrição seja executada, as colunas de chave estrangeira devem ser anuladas. Não podem ser especificadas para tabelas que tenham gatilhos INSTEAD OF UPDATE.

SET DEFAULT
Todos os valores que compõem a chave estrangeira são definidos para os seus valores por defeito se a linha correspondente na tabela pai for actualizada ou eliminada. Para que esta restrição seja executada, todas as colunas de chave estrangeira devem ter definições por defeito. Se uma coluna for nula, e não houver nenhum valor por defeito explícito definido, NULL torna-se o valor por defeito implícito da coluna. Não pode ser especificado para tabelas que tenham triggers INSTEAD OF UPDATE.

CASCADE, SET NULL, SET DEFAULT e NO ACTION podem ser combinados em tabelas que tenham relações de referência entre si. Se o Motor de Base de Dados não encontra QUALQUER ACÇÃO, pára e retrocede as acções relacionadas com CASCADE, SET NULL e SET DEFAULT. Quando uma declaração DELETE causa uma combinação de CASCADE, SET NULL, SET DEFAULT e NO ACTION, todas as acções CASCADE, SET NULL e SET DEFAULT são aplicadas antes que o Motor de Base de Dados verifique a existência de NO ACTION.

Acionadores e acções referenciais em cascata

As acções referenciais em cascata disparam o APAGAR ou ATRASAR da seguinte forma:

• Todas as acções referenciais em cascata directamente causadas pelo APAGAR ou ATUALIZAR original são executadas primeiro.

• p> Se houver algum APAGADOR APÓS definido nas tabelas afectadas, estes disparam depois de todas as acções em cascata serem executadas. Estes disparam por ordem oposta à da acção em cascata. Se houver gatilhos múltiplos numa única tabela, disparam 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 cadeias múltiplas em cascata tiverem origem na tabela que foi o alvo directo de uma acção de UPDATE ou DELETE, a ordem em que estas cadeias disparam os seus respectivos gatilhos não é especificada. Contudo, uma cadeia dispara sempre todos os seus gatilhos antes de outra cadeia começar a disparar.

• Um gatilho APÓS a tabela que é o alvo directo de uma acção UPDATE ou DELETE dispara independentemente de qualquer fila ser afectada. Não existem outras tabelas afectadas pelo cascading neste caso.

• Se qualquer um dos gatilhos anteriores efectuar operações UPDATE ou DELETE em outras tabelas, estas acções podem iniciar cadeias secundárias de cascata. Estas cadeias secundárias são processadas para cada operação UPDATE ou DELETE de cada vez, após todos os gatilhos em todas as cadeias primárias de fogo. Este processo pode ser repetido recursivamente para operações subsequentes de UPDATE ou DELETE.

• p>criar, ALTER, DELETE, ou outras operações de linguagem de definição de dados (DDL) dentro dos gatilhos podem causar o disparo de gatilhos DDL. Isto pode subsequentemente executar operações DELETE ou UPDATE que iniciam cadeias e triggers em cascata adicionais.
• p> se for gerado um erro dentro de qualquer cadeia de acção referencial em cascata em particular, um erro é levantado, não são disparados quaisquer triggers APÓS essa cadeia, e a operação DELETE ou UPDATE que criou a cadeia é enrolada de volta.
• p> Uma tabela que tenha um INSTEAD OF trigger não pode também ter uma cláusula REFERENCES que especifique uma acção em cascata. No entanto, um gatilho APÓS sobre uma tabela visada por uma acção em cascata pode executar uma declaração INSERIR, ACTUALIZAR, ou APAGAR noutra tabela ou visualizar que dispara um INSTEAD OF trigger definido nesse objecto.

Tarefas Relacionadas

A tabela seguinte lista as tarefas comuns associadas a restrições de chave primária e chave estrangeira.

>th>Topic

Descrifica como criar relações de chaves estrangeiras

>tr>>Descrifica como modificar relações de chaves estrangeiras.

Describes how to disable foreign key constraints for replication.

Task
Descrifica como criar uma chave primária.	Create Primary Keys
Describes how to delete a primary key.	Delete Primary Keys
Describes how to modify a primary key.	Modificar chaves primárias
Criar relações de chaves estrangeiras
Modificar Relações de Chave Estrangeira
Descrifica como eliminar relações de chave estrangeira.	Delete Foreign Key Relationships
Descrifica como visualizar propriedades de chave estrangeira.	View Foreign Key Properties
Disable Foreign Key Constraints for Replication
Describes how to disable foreign key constraints during an INSERT or UPDATE statement.	Disable Foreign Key Constraints with INSERT and UPDATE Statements