Como manter a integridade referencial em um banco de dados SQL Multitable

Mesmo se todas as mesas em seu sistema SQL tem a integridade da entidade e integridade de domínio, você ainda pode ter um problema por causa de inconsistências na forma como uma tabela refere-se a outro. Em bancos de dados multitable mais bem desenhados, cada tabela contém pelo menos uma coluna que se refere a uma coluna em outra tabela no banco de dados.

Estas referências são importantes para manter a integridade global da base de dados. As mesmas referências, no entanto, fazer atualização anomalias possível. anomalias de atualização são problemas que podem ocorrer depois de atualizar os dados em uma linha de uma tabela de banco de dados.

Problemas entre tabelas pai e filho

As relações entre as mesas não são geralmente bidireccional. Uma tabela é geralmente dependente da outra. Dizer que você tem um banco de dados com uma tabela de clientes e uma tabela Pedidos. Você pode entrar um cliente na tabela de cliente antes de ela faz ordens. Você não pode, no entanto, introduzir uma ordem na tabela ORDENS a menos que você já tenha uma entrada na tabela de cliente para o cliente que está fazendo nessa ordem.

A tabela ORDENS é dependente na tabela do CLIENTE. Este tipo de arranjo é frequentemente chamado de relação pai-filho, onde CLIENT é a tabela pai e ordens é a tabela filho. A criança é dependente da mãe.

Geralmente, a chave primária da tabela pai é uma coluna (ou grupo de colunas) que aparece na tabela filho. Dentro da tabela de criança, essa mesma coluna (ou grupo) é uma chave estrangeira. Tenha em mente, contudo, que uma chave estrangeira não precisa ser exclusivo.

anomalias de atualização surgem de várias maneiras entre as tabelas pai e filho. Um cliente se afasta, por exemplo, e você quer apagar a sua informação a partir do seu banco de dados. Se ela já fez alguns pedidos (que você gravou na tabela de encomendas), apagando-a da mesa de cliente poderia apresentar um problema.

Você teria registros na tabela Pedidos (filho) para o qual você não tem registros correspondentes na tabela CLIENTE (pai). Problemas semelhantes podem surgir se você adicionar um registro a uma tabela filho sem fazer uma adição correspondente para a tabela pai.

As chaves estrangeiras correspondentes em todas as tabelas filho deve refletir quaisquer alterações à chave primária de uma linha em uma mesa- pai de outra forma o resultado é uma atualização anomalia.

Use cascata exclusões com cuidado

Você pode eliminar a maioria dos problemas de integridade referencial, controlando cuidadosamente o processo de atualização. Em alguns casos, você tem que cascata deleções de uma tabela pai para seus filhos. Em cascata uma exclusão quando você exclui uma linha de uma tabela pai, você também exclui todas as linhas de suas tabelas filho cujas chaves estrangeiras coincidir com a chave primária da linha excluída na tabela pai.

Dê uma olhada no exemplo a seguir:

CRIAR CLIENTE TABLE (NomeCliente CHAR (30) CHAVE PRIMÁRIA, Address1 CHAR (30), Endereço2 CHAR (30), CityCHAR (25) NOT NULL, StateCHAR (2), PostalCode CHAR (10), PhoneCHAR (13), Fax CHAR ( 13), Contactperson CHAR (30)) TESTES DE MESA -Criar (TestName CHAR (30) PRIMARY KEY, StandardCharge CHAR (30)) -Criar tabela de funcionários (EmployeeName CHAR (30) CHAVE PRIMÁRIA, ADDRESS1 CHAR (30), Endereço2 CHAR ( 30), CityCHAR (25), StateCHAR (2), PostalCode CHAR (10), HomePhone CHAR (13), OfficeExtension CHAR (4), HireDate DATA, JobClassification CHAR (10), HourSalComm CHAR (1)) tabela pedidos -Criar (OrderNumber INTEGER PRIMARY KEY, NomeCliente CHAR (30), TestOrdered CHAR (30), Vendedor de CHAR (30), OrderDate DATA, CONSTRAINT NameFK FOREIGN KEY (CLIENTNAME) referências de clientes (NomeCliente) ON DELETE CASCADE, CONSTRAINT TestFK FOREIGN KEY (TestOrdered) Referências testes (TestName) ON DELETE CASCADE, CONSTRAINT SalesFK FOREIGN KEY (vendedor) REFERÊNCIAS DOS TRABALHADORES (EmployeeName) ON DELETE CASCADE) -

a restrição NameFK nomes Nome do cliente como uma chave estrangeira que faz referência a Nome do cliente coluna na tabela do CLIENTE. Se você excluir uma linha na tabela de cliente, você também excluir automaticamente todas as linhas na tabela Pedidos que têm o mesmo valor no Nome do cliente coluna como aqueles na Nome do cliente coluna da tabela de CLIENTE.

A deleção em cascata para baixo da tabela CLIENTE à tabela de pedidos. O mesmo é verdadeiro para as chaves estrangeiras na tabela Pedidos que se referem às chaves primárias dos ensaios e das tabelas EMPREGADOS.

formas alternativas para controlar anomalias de atualização

Você não pode querer cascata uma deleção. Em vez disso, você pode querer mudar a chave estrangeira da tabela filho a um NULO valor. Considere o seguinte variante:

Criar ordens de mesa (OrderNumber INTEIRO Chave primária, NomeCliente CHAR (30), TestOrdered CHAR (30), vendedor CHAR (30), OrderDate DATA, CONSTRAINT NameFK FOREIGN KEY (CLIENTNAME) referências de clientes (NomeCliente), CONSTRAINT TestFK FOREIGN KEY (TestOrdered) Referências testes (TestName), CONSTRAINT SalesFK FOREIGN KEY (vendedor) REFERÊNCIAS DOS TRABALHADORES (EmployeeName) ON DELETE SET NULL) -

a restrição SalesFK nomes do vendedor coluna como uma chave estrangeira que faz referência a Nome do empregado coluna da tabela EMPREGADOS. Se um vendedor deixa a empresa, você excluir sua linha na tabela EMPREGADOS. Novos vendedores são eventualmente atribuídos a seus contas, mas por agora, a exclusão de seu nome da tabela EMPREGADOS faz com que todas as suas ordens na tabela de pedidos para receber um valor nulo no vendedor coluna.

Você também pode manter dados inconsistentes fora de um banco de dados usando um dos seguintes métodos:

• Recusar-se a permitir uma adição a uma tabela filho até que existe uma linha correspondente na tabela pai. Se você se recusar a permitir linhas em uma tabela filho sem uma linha correspondente na tabela pai, você prevenir a ocorrência de # 147-orphan # 148- linhas na tabela filho. Esta recusa ajuda a manter a consistência entre tabelas.

• Recusar-se a permitir alterações a chave primária de uma tabela. Se você se recusar a permitir alterações a chave primária de uma tabela, você não precisa se preocupar em atualizar as chaves estrangeiras em outras tabelas que dependem desse chave primária.