Comment maintenir l'intégrité référentielle dans une base de données SQL multitable

Même si chaque table dans votre système de SQL a l'intégrité de l'entité et de l'intégrité de domaine, vous pouvez toujours avoir un problème en raison d'incohérences dans la façon dont une table rapporte à un autre. Dans les bases de données multi-tables plus bien conçus, chaque table contient au moins une colonne qui fait référence à une colonne dans une autre table dans la base de données.

Sommaire

Ces références sont importants pour maintenir l'intégrité globale de la base de données. Les mêmes références, cependant, faire la mise à jour d'éventuelles anomalies. Mettez à jour les anomalies sont des problèmes qui peuvent survenir après que vous mettez à jour les données dans une rangée d'une table de base de données.

Problème entre parent et enfant tables

Les relations entre les tables ne sont généralement pas bidirectionnel. Une table est généralement dépendant de l'autre. Dire que vous avez une base de données avec une table de client et un tableau de commandes. Vous pouvez entrer un client dans la table du client avant elle fait commandes. Vous ne pouvez pas, cependant, de saisir un ordre dans la table COMMANDES sauf si vous avez déjà une entrée dans la table de client pour le client qui gagne cet ordre.

La table des commandes dépend de la table Client. Ce genre d'arrangement est souvent appelé un relation parent-enfant, où le client est la table parent et ORDONNE est la table enfant. L'enfant est la charge du parent.

En règle générale, la clé primaire de la table parent est une colonne (ou d'un groupe de colonnes) qui apparaît dans la table enfant. Dans le table enfant, cette même colonne (ou un groupe) est une clé étrangère. Gardez à l'esprit, cependant, que d'une clé étrangère ne doit pas être unique.

Mettez à jour les anomalies surviennent dans plusieurs façons entre tables parent et enfant. Un client déménage, par exemple, et que vous souhaitez supprimer ses informations à partir de votre base de données. Si elle a déjà fait quelques ordres (lesquels ont été enregistrés dans la table ORDERS), sa suppression de la table de client pourrait présenter un problème.




Vous auriez enregistrements de la table COMMANDES (enfant) pour lesquels vous avez aucun enregistrements correspondants dans le CLIENT (parent) table. Des problèmes similaires peuvent survenir si vous ajoutez un enregistrement à une table enfant sans faire une addition correspondant à la table parent.

Les clés étrangères correspondantes dans toutes les tables enfant doivent tenir compte des changements à la clé primaire d'une ligne dans une table- mère Sinon, une mise à jour des résultats d'anomalies.

Utilisez des soins suppressions en cascade

Vous pouvez éliminer la plupart des problèmes d'intégrité référentielle en contrôlant attentivement le processus de mise à jour. Dans certains cas, vous devez cascade les suppressions d'une table de mère à ses enfants. Pour mettre en cascade une suppression lorsque vous supprimez une ligne d'une table parent, vous supprimez également toutes les lignes de ses tableaux de l'enfant dont les clefs étrangères correspondre à la clé primaire de la ligne supprimée dans la table parent.

Jetez un oeil à l'exemple suivant:

CREATE TABLE CLIENT (ClientName CHAR (30) PRIMARY KEY, Address1 CHAR (30), Address2 CHAR (30), CityCHAR (25) NOT NULL, StateCHAR (2), PostalCode CHAR (10), PhoneCHAR (13), fax CHAR ( 13), ContactPerson CHAR (30)) TESTS DE TABLE -Créer (TestName CHAR (30) PRIMARY KEY, StandardCharge CHAR (30)) -Créer TABLE DES EMPLOYÉS (EmployeeName CHAR (30) PRIMARY KEY, ADDRESS1 CHAR (30), Address2 CHAR ( 30), CityCHAR (25), StateCHAR (2), PostalCode CHAR (10), HomePhone CHAR (13), OfficeExtension CHAR (4), HireDate DATE, JobClassification CHAR (10), HourSalComm CHAR (1)) -Créer TABLE commandes (OrderNumber INTEGER PRIMARY KEY, ClientName CHAR (30), TestOrdered CHAR (30), Salesperson CHAR (30), OrderDate DATE, CONTRAINTE NameFK clé étrangère (ClientName) Références CLIENT (ClientName) suppression en cascade, la contrainte TestFK FOREIGN KEY (TestOrdered) Références TESTS (Nomtest) suppression en cascade, la contrainte SalesFK FOREIGN KEY (Salesperson) RÉFÉRENCES EMPLOYÉ (EmployeeName) suppression en cascade) -

La contrainte NameFK noms Nom du client comme une clé étrangère qui fait référence à la Nom du client colonne dans la table CLIENT. Si vous supprimez une ligne dans le tableau de client, vous supprimez aussi automatiquement toutes les lignes de la table des commandes qui ont la même valeur dans le Nom du client colonne que celles de la Nom du client colonne de la table Client.

Les cascades de suppression en bas de la table CLIENT à la table Orders. La même chose est vraie pour les clés étrangères dans la table ORDERS qui se réfèrent à des clés primaires des essais et des tables de l'employé.

Autres moyens de contrôler la mise à jour des anomalies

Vous ne pouvez pas vouloir une suppression en cascade. Au lieu de cela, vous voudrez peut-être changer de clé étrangère de la table enfant à un NUL valeur. Considérons la variante suivante:

CREATE TABLE commandes (OrderNumber ENTIER clé primaire, ClientName CHAR (30), TestOrdered CHAR (30), SalesPerson CHAR (30), OrderDate DATE, CONTRAINTE NameFK clé étrangère (ClientName) Références CLIENT (ClientName), la contrainte TestFK FOREIGN KEY (TestOrdered) Références TESTS (Nomtest), la contrainte SalesFK FOREIGN KEY (Salesperson) RÉFÉRENCES EMPLOYÉ (EmployeeName) sur Supprimer SET NULL) -

La contrainte SalesFK noms du Vendeur colonne comme une clé étrangère qui fait référence à la Nom de l'employé colonne de la table EMPLOYEE. Si un vendeur quitte l'entreprise, vous supprimez son rang dans la table EMPLOYEE. Nouveaux vendeurs sont finalement affectés à ses comptes, mais pour l'instant, la suppression de son nom de la table EMPLOYEE provoque l'ensemble de ses commandes de la table de afin de recevoir une valeur nulle dans le Vendeur colonne.

Vous pouvez également conserver des données incohérentes sur une base de données en utilisant une de ces méthodes:

  • Refuser de permettre à un ajout à une table enfant jusqu'à ce qu'une ligne correspondante existe dans sa table parent. Si vous refusez de permettre lignes dans une table enfant sans ligne correspondante dans une table parent, vous empêchez l'apparition de “ orphelin ” les lignes de la table enfant. Ce refus contribue à maintenir la cohérence entre les tables.

  • Refuser de permettre des modifications à la clé primaire d'une table. Si vous refusez de permettre des changements à la clé primaire d'une table, vous ne devez pas vous inquiéter à propos de la mise à jour des clés étrangères dans les autres tableaux qui dépendent de cette clé primaire.


» » » » Comment maintenir l'intégrité référentielle dans une base de données SQL multitable