Que sont les clés étrangères SQL. Qu'est-ce qu'une clé primaire dans une base de données ? plusieurs à plusieurs

ELLE S'APPLIQUE À : SQL Server (depuis 2016)Azure SQL DatabaseAzure SQL Data WarehouseParallel Data Warehouse

Les clés primaires et les clés étrangères sont deux types de contraintes qui peuvent être utilisées pour garantir l'intégrité des données dans les tables SQL Server. Ce sont des objets de base de données importants.

Ce sujet est abordé dans les sections suivantes.

Contraintes de clé primaire

Contraintes de clé étrangère

Tâches associées

Généralement, un tableau comporte une colonne ou une combinaison de colonnes contenant des valeurs qui identifient de manière unique chaque ligne du tableau. Cette ou ces colonnes sont appelées clé primaire (PK) de la table et assurent l'intégrité de l'entité de la table. Les contraintes de clé primaire sont souvent définies sur une colonne d'identité car elles garantissent que les données sont uniques.

Lorsque vous définissez une contrainte de clé primaire sur une table dans un composant, le moteur de base de données garantit que les données sont uniques en créant automatiquement un index unique sur les colonnes de clé primaire. Cet index fournit également un accès rapide aux données lors de l'utilisation de la clé primaire dans les requêtes. Si une contrainte de clé primaire est définie sur plusieurs colonnes, les valeurs peuvent être dupliquées au sein de la même colonne, mais chaque combinaison des valeurs de toutes les colonnes dans la définition de contrainte de clé primaire doit être unique.

Comme le montre la figure suivante, les colonnes ID produit Et Fournisseur ID dans la table Achats.ProductVendor former une contrainte de clé primaire composée pour une table donnée. Cela garantit que chaque ligne du tableau Fournisseur de produits a une combinaison unique de significations ID produit Et Fournisseur ID. Cela empêche l'insertion de lignes en double.

Une table ne peut avoir qu’une seule contrainte de clé primaire.

La clé primaire ne peut pas comporter plus de 16 colonnes et la longueur totale de la clé ne peut pas dépasser 900 octets.

Un index formé par une contrainte de clé primaire ne peut pas faire en sorte que le nombre d'index dans la table dépasse la limite de 999 index non clusterisés et 1 index clusterisé.

Si une contrainte de clé primaire ne précise pas si l'index est clusterisé ou non clusterisé, un index clusterisé est créé s'il n'en existe pas sur la table.

Toutes les colonnes avec une contrainte de clé primaire doivent être définies comme non nullables. Si la possibilité de nullité n'est pas spécifiée, toutes les colonnes avec une contrainte de clé primaire sont définies comme non nullables.

Si la clé primaire est définie sur une colonne d'un type de données défini par l'utilisateur CLR, l'implémentation de ce type doit prendre en charge le tri binaire.

Une clé étrangère (FK) est une colonne ou une combinaison de colonnes utilisée pour forcer une relation entre les données de deux tables afin de contrôler les données pouvant être stockées dans la table de clé étrangère. Si une ou plusieurs colonnes contenant la clé primaire d'une table sont référencées dans une ou plusieurs colonnes d'une autre table, un lien de clé étrangère crée une relation entre les deux tables. Cette colonne devient une clé étrangère dans la deuxième table.

Par exemple, le tableau Sales.SalesOrderHeader lié au tableau Ventes.SalesPerson en utilisant une clé étrangère car il existe une relation logique entre les commandes clients et les responsables commerciaux. Colonne ID du vendeur dans la table Sales.SalesOrderHeader correspond à la colonne de clé primaire du tableau Vendeur. Colonne ID du vendeur dans la table Sales.SalesOrderHeader est une clé étrangère de la table Vendeur. En établissant cette relation à l'aide d'une clé étrangère, la valeur de ID du vendeur ne peut pas être inséré dans le tableau En-tête de commande de vente, s'il n'est pas actuellement contenu dans le tableau Vendeur.

Le nombre maximum de tables et de colonnes qu'une table peut référencer en tant que clés étrangères (références sortantes) est de 253. SQL Server 2016 augmente la limite du nombre d'autres tables et colonnes pouvant référencer des colonnes dans la même table (références entrantes), de 253 jusqu'à 10 000. (Nécessite un niveau de compatibilité d'au moins 130.) Le grossissement est soumis aux limitations suivantes :

Le dépassement de 253 références de clé étrangère n’est pris en charge que pour les opérations DML DELETE. Les opérations UPDATE et MERGE ne sont pas prises en charge.

Le dépassement de 253 références de clé étrangère n'est actuellement pas disponible pour les index columnstore, les tables à mémoire optimisée, la base de données Stretch ou les tables partitionnées par clé étrangère.

Index dans les contraintes de clé étrangère

Contrairement aux contraintes de clé primaire, lorsque vous créez une contrainte de clé étrangère, un index correspondant n'est pas automatiquement créé. Cependant, il est souvent nécessaire de créer manuellement un index sur une clé étrangère pour les raisons suivantes :

Les colonnes de clé étrangère sont souvent utilisées dans les critères de jointure lorsqu'elles sont utilisées ensemble pour interroger des données à partir de tables associées. Ceci est implémenté en mappant une ou plusieurs colonnes d’une contrainte de clé étrangère dans une table à une ou plusieurs colonnes de clé primaire ou unique dans une autre table. Un index permet au moteur de base de données de trouver rapidement des données associées dans une table de clé étrangère. Cependant, la création d'un index n'est pas obligatoire. Les données de deux tables liées peuvent être combinées même s'il n'y a pas de contraintes de clé primaire ou de clé étrangère définies entre les tables, mais une relation de clé étrangère entre deux tables indique que les deux tables sont optimisées pour être utilisées ensemble dans une requête qui utilise les clés. comme critères.

Les contraintes de clé étrangère vérifient les modifications apportées aux contraintes de clé primaire sur les tables associées.

Intégrité référentielle

L'objectif principal d'une contrainte de clé étrangère est de contrôler les données pouvant être stockées dans la table de clé étrangère, mais la contrainte contrôle également les modifications apportées aux données de la table de clé primaire. Par exemple, si vous supprimez la ligne du directeur commercial du tableau Ventes.SalesPerson, dont l'ID est utilisé dans les commandes client dans la table Sales.SalesOrderHeader, l'intégrité référentielle des deux tables sera violée. Commandes client du gestionnaire à distance dans le tableau En-tête de commande de vente deviendra invalide sans connexion aux données de la table Vendeur.

Une contrainte de clé étrangère empêche cette situation de se produire. Une contrainte applique l'intégrité référentielle de la manière suivante : elle empêche les modifications des données dans la table de clé primaire si de telles modifications invalident une référence dans la table de clé étrangère. Si vous essayez de supprimer une ligne dans une table de clé primaire ou de modifier la valeur de cette clé, si vous constatez que la valeur de clé primaire supprimée ou modifiée a une valeur correspondante dans une contrainte de clé étrangère dans une autre table, l'action échouera. Pour réussir à modifier ou supprimer une ligne avec une contrainte de clé étrangère, vous devez d'abord supprimer les données de clé étrangère dans la table de clé étrangère ou modifier les données de la table de clé étrangère qui relient la clé étrangère aux données d'une autre clé primaire.

Intégrité référentielle en cascade

En utilisant des contraintes d'intégrité référentielle en cascade, vous pouvez définir les actions que le moteur de base de données entreprendra lorsqu'un utilisateur tentera de supprimer ou de mettre à jour une clé pointée par des clés étrangères qui existent encore. Les actions en cascade suivantes peuvent être définies.

PAS D'ACTION
Le moteur de base de données génère une erreur, puis annule l'opération de suppression ou de mise à jour sur la ligne de la table parent.

CASCADE
Les lignes correspondantes sont mises à jour ou supprimées de la table de référencement si cette ligne est mise à jour ou supprimée de la table parent. La valeur CASCADE ne peut pas être spécifiée si la colonne est de type horodatage fait partie d’une clé étrangère ou de référence. L’action ON DELETE CASCADE ne peut pas être spécifiée sur une table sur laquelle un déclencheur INSTEAD OF DELETE est défini. La clause ON UPDATE CASCADE ne peut pas être spécifiée sur les tables sur lesquelles des déclencheurs INSTEAD OF UPDATE sont définis.

FIXER NULL
Toutes les valeurs qui composent une clé étrangère sont définies sur NULL lorsque la ligne correspondante de la table parent est mise à jour ou supprimée. Pour satisfaire à cette restriction, les colonnes de clé étrangère doivent pouvoir être nullables. Ne peut pas être défini sur les tables sur lesquelles des déclencheurs INSTEAD OF UPDATE sont définis.

DÉFINIR PAR DEFAUT
Toutes les valeurs qui composent une clé étrangère sont définies sur leur valeur par défaut lorsque la ligne correspondante de la table parent est supprimée ou mise à jour. Pour satisfaire cette contrainte, toutes les colonnes de clé étrangère doivent avoir des définitions par défaut. Si une colonne est nullable et que la valeur par défaut n'est pas explicitement définie, la valeur par défaut de la colonne devient NULL. Ne peut pas être défini sur les tables sur lesquelles des déclencheurs INSTEAD OF UPDATE sont définis.

Les mots-clés CASCADE, SET NULL, SET DEFAULT et NO ACTION peuvent être combinés dans des tables ayant des relations de référence mutuelle. Si le moteur de base de données rencontre le mot clé NO ACTION, il arrêtera et annulera les opérations CASCADE, SET NULL et SET DEFAULT associées. Si une instruction DELETE contient une combinaison des mots clés CASCADE, SET NULL, SET DEFAULT et NO ACTION, toutes les opérations CASCADE, SET NULL et SET DEFAULT sont effectuées avant que le moteur de base de données recherche l'opération NO ACTION.

Déclencheurs et actions de référence en cascade

Les actions de référence en cascade se déclenchent après UPDATE ou AFTER DELETE comme suit :

Toutes les actions de référence en cascade directement provoquées par les instructions DELETE ou UPDATE d'origine sont exécutées en premier.

Si des déclencheurs AFTER sont définis sur des tables qui ont été modifiés, ces déclencheurs sont déclenchés une fois toutes les actions en cascade terminées. Ces déclencheurs se déclenchent dans l’ordre inverse des actions en cascade. Si plusieurs déclencheurs sont définis pour une seule table, ils sont déclenchés dans un ordre aléatoire, sauf si le premier et le dernier déclencheurs de la table sont sélectionnés. Cet ordre est déterminé par la procédure.

Si les séquences d'actions en cascade proviennent d'une table qui était la cible immédiate d'une action DELETE ou UPDATE, l'ordre dans lequel les séquences d'actions déclenchent les déclencheurs n'est pas déterminé. Cependant, une séquence d'actions déclenche toujours tous ses déclencheurs avant la suivante.

Un déclencheur AFTER sur une table qui était la cible immédiate d'une action DELETE ou UPDATE se déclenche, que des lignes aient ou non été modifiées. Dans ce cas, aucune autre table n'est affectée par la mise en cascade.

Si l'un des déclencheurs précédents effectue des opérations DELETE ou UPDATE sur d'autres tables, ces opérations peuvent déclencher leur propre séquence d'actions en cascade. Ces séquences d'actions secondaires sont traitées pour chaque opération DELETE ou UPDATE une fois que tous les déclencheurs de séquence d'actions principales sont terminés. Ce processus peut être répété de manière récursive pour les opérations DELETE ou UPDATE ultérieures.

L'exécution de CREATE, ALTER, DELETE ou d'autres opérations DDL à l'intérieur de déclencheurs peut provoquer le déclenchement de déclencheurs DDL. Cela peut conduire à d'autres opérations DELETE ou UPDATE, qui lanceront des séquences supplémentaires d'actions en cascade et déclencheront leurs déclencheurs.

Si une erreur se produit dans une séquence particulière d'actions de référence en cascade, aucun déclencheur AFTER ne sera déclenché sur cette séquence et les opérations DELETE ou UPDATE générées par cette séquence seront annulées.

Une table dans laquelle un déclencheur INSTEAD OF est défini peut également avoir une clause REFERENCES qui spécifie une action en cascade spécifique. Cependant, un déclencheur AFTER sur la table cible d'une action en cascade peut émettre une instruction INSERT, UPDATE ou DELETE sur une autre table ou vue, qui déclenchera un déclencheur INSTEAD OF sur cet objet.

Le tableau suivant répertorie les tâches courantes associées aux contraintes de clé primaire et de clé étrangère.

Et ainsi, tranquillement, nous avons abordé un sujet très important : les clés primaires et étrangères. Si les premiers sont utilisés par presque tout le monde, les seconds sont en quelque sorte ignorés. Mais en vain. Les clés étrangères ne posent pas de problème, elles sont une réelle aide à l'intégrité des données.

1.2.5. Clé primaire

Nous avons déjà beaucoup parlé des champs clés, mais nous ne les avons jamais utilisés. Le plus intéressant c’est que tout a fonctionné. C'est un avantage, ou peut-être un inconvénient, des bases de données Microsoft SQL Server et MS Access. Cette astuce ne fonctionnera pas dans les tables Paradox et sans champ clé, la table sera en lecture seule.

Dans une certaine mesure, les clés sont des contraintes et elles pourraient être considérées conjointement avec l'instruction CHECK car la déclaration se produit de la même manière et utilise même l'instruction CONSTRAINT. Examinons ce processus avec un exemple. Pour ce faire, nous allons créer un tableau de deux champs « guid » et « vcName ». Cela définit le champ « guid » comme clé primaire :

CREATE TABLE Globally_Unique_Data (guid uniqueidentifier DEFAULT NEWID(), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (Guid))

La meilleure partie ici est la ligne CONSTRAINT. Comme nous le savons, après ce mot-clé vient le nom de la contrainte, et la déclaration de clé ne fait pas exception. Pour nommer une clé primaire, je recommande d'utiliser un nom comme PK_name, où nom est le nom du champ qui doit devenir la clé primaire. L'abréviation PK vient de Primary Key.

Après cela, à la place du mot-clé CHECK, que nous avons utilisé dans les contraintes, il y a un opérateur PRIMARY KEY, c'est ce qui indique que nous n'avons pas besoin d'un contrôle, mais d'une clé primaire. Un ou plusieurs champs qui composeront la clé sont indiqués entre parenthèses.

N'oubliez pas que deux lignes ne peuvent pas avoir la même valeur dans un champ clé, dans lequel une contrainte de clé primaire est identique à une contrainte d'unicité. Cela signifie que si vous faites du champ de stockage du nom de famille la clé primaire, il ne sera pas possible d'écrire deux Ivanov avec des noms différents dans une telle table. Cela viole la contrainte de clé primaire. C'est pourquoi les clés sont des contraintes et sont déclarées de la même manière qu'une contrainte CHECK. Mais cela n’est pas vrai uniquement pour les clés primaires et secondaires avec unicité.

Dans cet exemple, la clé primaire est un champ de type uniqueidentifier (GUID). La valeur par défaut de ce champ est le résultat de la procédure serveur NEWID.

Attention

Une seule clé primaire peut être créée pour une table

Pour simplifier les exemples, il est conseillé d'utiliser un type numérique comme clé, et si la base de données le permet, il sera préférable qu'elle soit du type « auto-incrémentation » (nombre automatiquement croissant/décroissant). Dans MS SQL Server, ce champ est IDENTITÉ, et dans MS Access, c'est un champ de type « compteur ».

L'exemple suivant montre comment créer une table product avec un champ entier à incrémentation automatique comme clé primaire :

CREATE TABLE Produits (id int IDENTITY(1, 1), product varchar(50), Price money, Quantité numérique(10, 2), CONSTRAINT PK_id PRIMARY KEY (id))

C'est le type de clé que nous utiliserons le plus souvent, car le champ clé stockera des nombres faciles à comprendre et sera plus facile et plus visuel à utiliser.

Une clé primaire peut comprendre plusieurs colonnes. L'exemple suivant crée une table dans laquelle les champs "id" et "Product" forment la clé primaire, ce qui signifie qu'un index unique sera créé sur les deux champs :

CREATE TABLE Products1 (id int IDENTITY(1, 1), Product varchar(50), Price money, Quantité numérique(10, 2), CONSTRAINT PK_id PRIMARY KEY (id, [Nom du produit]))

Très souvent, les programmeurs créent une base de données avec un champ clé sous la forme d'un entier, mais en même temps, la tâche indique clairement que certains champs doivent être uniques. Pourquoi ne pas créer immédiatement une clé primaire à partir des champs qui doivent être uniques et il ne sera pas nécessaire de créer des solutions distinctes pour ce problème.

Le seul inconvénient d'une clé primaire multicolonne est le problème de la création de relations. Ici, vous devez vous en sortir en utilisant diverses méthodes, mais le problème peut toujours être résolu. Il vous suffit de saisir un champ de type identifiant unique et d'établir une connexion en l'utilisant. Oui, dans ce cas nous obtenons une clé primaire unique et un champ de type uniqueidentifier, mais cette redondance ne sera donc pas supérieure à la même table où la clé primaire est uniqueidentifier, et une contrainte d'unicité est définie sur les champs qui doivent être unique. Que choisir ? Cela dépend de la tâche spécifique et de ce avec quoi vous êtes plus à l'aise de travailler.

1.2.6. Clé externe

Une clé étrangère est également une contrainte CONSTRAINT et représente la relation entre deux tables. Disons que vous avez deux tables :

Noms – contient les noms des personnes et se compose de champs d'identification (champ clé), de nom.
Phones est une table téléphonique composée d'un identifiant (champ clé), d'une clé étrangère pour se connecter à la table des noms et d'un champ de chaîne pour stocker le numéro de téléphone.

Une personne peut posséder plusieurs téléphones, nous avons donc divisé le stockage des données en différentes tables. La figure 1.4 montre visuellement la relation entre deux tables. Si vous avez déjà travaillé avec des tables liées, cela vous suffira. Si vous entendez parler de connexions pour la première fois, essayons d’examiner le problème de plus près.

Par exemple, prenons une table de trois personnes. Le tableau 1.3 montre le contenu du tableau "Noms". Il n'y a que trois lignes et chacune a son propre passe-partout. Pour des raisons d'unicité, lorsque nous créons une table, nous ferons de la clé un champ croissant automatiquement.

Tableau 1.3 Contenu du tableau Noms

Tableau 1.4. Contenu du tableau Téléphones

Le tableau 1.4 contient cinq numéros de téléphone. Le champ de clé principale contient également une clé principale unique, qui peut également être incrémentée automatiquement. Une clé secondaire est une relation avec la clé primaire de la table Names. Comment fonctionne cette connexion ? Petrov a le numéro 1 comme clé primaire dans la table Noms. Dans la table Téléphones, dans la clé secondaire, nous recherchons le numéro 1 et obtenons les numéros de téléphone de Petrov. Il en va de même pour le reste des entrées. Visuellement, la connexion est visible sur la figure 1.5.

Ce type de stockage de données est très pratique. S'il n'était pas possible de créer des tables associées, dans la table Noms, nous devrions saisir tous les numéros de téléphone dans un seul champ. Ceci est gênant du point de vue de l'utilisation, de la maintenance et de la récupération des données.

Vous pouvez créer plusieurs champs Noms dans un tableau, mais la question se pose : combien. Une personne ne peut avoir qu'un seul téléphone, mais moi, par exemple, j'en ai 3, sans compter ceux du travail. Un grand nombre de champs entraîne une redondance des données.

Vous pouvez créer une ligne distincte avec le nom de famille de chaque téléphone dans le tableau Noms, mais cela n'est facile que pour un exemple aussi simple, lorsque vous n'avez besoin que de saisir le nom de famille et que vous pouvez facilement faire plusieurs entrées pour Petrov avec plusieurs téléphones. Nombres. Et s'il y a 10 ou 20 champs ? Ainsi, la création de deux tables liées par une clé étrangère est visible dans le listing 1.6.

Inscription 1.6. Créer des tables liées par une clé étrangère

CREATE TABLE Noms (idName int IDENTITY(1,1), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName),) CREATE TABLE Phones (idPhone int IDENTITY(1,1), idName int, vcPhone varchar(10), CONSTRAINT PK_idPhone CLÉ PRIMAIRE (idPhone), CONTRAINTE FK_idName CLÉ ÉTRANGÈRE (idName) RÉFÉRENCES Noms (idName))

Veuillez examiner attentivement le contenu de l'annonce. C'est assez intéressant car il utilise certains des opérateurs que nous avons déjà abordés et un exemple supplémentaire serait utile. Pour les deux tables, un champ clé est créé, qui vient en premier, est de type int et est automatiquement incrémenté, à partir de 1 par incréments de un. Le champ clé devient la clé principale à l'aide d'une contrainte CONSTRAINT.

Dans la description de la table Phones, la dernière ligne contient une nouvelle déclaration pour nous, à savoir la déclaration d'une clé étrangère utilisant l'opérateur FOREIGN KEY. Comme vous pouvez le constater, c’est aussi une limitation et un peu plus tard vous verrez pourquoi. Le champ de la table qui doit être lié à une autre table est indiqué entre parenthèses. Vient ensuite le mot clé REFERENCES (lien), le nom de la table avec laquelle la connexion doit être établie (Names) et entre parenthèses le nom du champ ("idName"). Ainsi, nous avons établi une connexion, illustrée à la figure 1.4.

Attention!

Une clé étrangère ne peut faire référence qu'à la clé primaire d'une autre table ou à une contrainte unique. Cela signifie qu'après le mot clé REFERENCES il doit y avoir un nom de table et seule une clé primaire ou un champ avec une contrainte UNIQUE peut être spécifié entre parenthèses. D'autres champs ne peuvent pas être spécifiés.

Maintenant, si vous pouvez remplir les tableaux avec des données. Les trois équipes suivantes ajoutent les trois noms que nous avons vus dans le tableau 1.3 :

INSERT INTO Names(vcName) VALUES("Petrov") INSERT INTO Names(vcName) VALUES("Ivanov") INSERT INTO Names(vcName) VALUES("Sidorov")

Si vous avez déjà travaillé avec SQL, vous pouvez ajouter des entrées pour la table téléphonique. Je vais omettre ces commandes, mais vous pouvez les voir dans le fichier Foreign_keys.sql dans le répertoire Chapter1 du CD.

Notre tâche est maintenant de voir quelles sont les actions restrictives d'une clé étrangère, découvrons-le. Nous avons spécifié une relation explicite entre deux champs dans des tables différentes. Si vous essayez d'ajouter un enregistrement à la table téléphonique avec un identifiant dans le champ "idName" qui n'existe pas dans le champ du même nom (le nom pourrait être changé en un autre) dans la table des noms de famille, une erreur se produira . Cela rompra la relation entre les deux tables et la contrainte de clé étrangère ne permettra pas aux enregistrements d'exister sans la relation.

La restriction s'applique également lors de la modification ou de la suppression d'enregistrements. Par exemple, si vous essayez de supprimer une ligne portant le nom de famille Petrov, une erreur de contrainte de clé étrangère se produira. Vous ne pouvez pas supprimer des enregistrements comportant des lignes liées en externe. Tout d’abord, vous devez supprimer tous les numéros de téléphone de cette entrée, et seulement après cela, il sera possible de supprimer la ligne portant le nom de famille Petrov.

Lors de la création d'une clé étrangère, vous pouvez spécifier ON DELETE CASCADE ou ON UPDATE CASCADE. Dans ce cas, si vous supprimez l'enregistrement de Petrov de la table Noms ou modifiez l'identifiant, alors tous les enregistrements de la table Téléphones associés à la ligne de Petrov seront automatiquement mis à jour. Jamais. Non, il faut l’écrire en majuscules : NE JAMAIS faire ça. Tout doit être supprimé ou modifié manuellement. Si un utilisateur supprime accidentellement une entrée du tableau Noms, les téléphones correspondants sont également supprimés. Cela ne sert à rien de créer une clé étrangère si la moitié de ses restrictions disparaissent ! Tout doit être fait manuellement et il n'est jamais recommandé de modifier les identifiants.

La suppression des tables elles-mêmes doit également commencer par la table subordonnée, c'est-à-dire Téléphones, et ce n'est qu'alors que vous pourrez supprimer la table principale Noms.

Enfin, je vais vous montrer comment obtenir magnifiquement une correspondance entre les noms et les numéros de téléphone de deux tableaux :

SELECT vcName, vcPhone FROM Noms, Téléphones OÙ Names.idName=Phones.idName

Nous parlerons de ces requêtes plus en détail au chapitre 2. Pour l'instant, j'ai donné un exemple juste pour que vous puissiez voir la puissance des tables associées.

Une table peut contenir jusqu'à 253 clés étrangères, ce qui est largement suffisant même pour les bases de données les plus complexes. Personnellement, j'ai dû travailler avec des bases de données où le nombre de clés étrangères ne dépassait pas 7 par table. Si c'est plus, il est fort probable que la base de données soit mal conçue, bien qu'il existe des exceptions.

La table elle-même peut également contenir un maximum de 253 clés étrangères. Les clés étrangères dans une table sont moins courantes, généralement pas plus de 3. Le plus souvent, une table peut avoir de nombreux liens vers d'autres tables.

Une clé étrangère peut référencer la même table dans laquelle elle est créée. Par exemple, vous disposez d'un tableau des titres de poste dans une organisation, comme indiqué dans le tableau 1.5. Le tableau se compose de trois champs : clé primaire, clé étrangère et titre du poste. Toute organisation peut avoir plusieurs postes, mais il serait tout à fait logique d'afficher leurs noms et leur structure de subordination dans un seul tableau. Pour ce faire, la clé étrangère doit être associée à la clé primaire de la table de positions.

Tableau 1.5. Tableau avec lien interne

En conséquence, nous obtenons que le directeur général a une clé étrangère nulle, c'est-à-dire cette position est en tête de toutes les autres. Pour le Directeur commercial et le Directeur des affaires générales, la clé étrangère pointe vers le rang du Directeur général. Cela signifie que ces deux postes relèvent directement du PDG. Et ainsi de suite.

Voyons comment nous pouvons créer tout cela sous forme de requête SQL :

CREATE TABLE Positions (idPosition int IDENTITY(1,1), idParentPosition int, vcName varchar(30), CONSTRAINT PK_idPosition PRIMARY KEY (idPosition), CONSTRAINT FK_idParentPosition FOREIGN KEY (idParentPosition) RÉFÉRENCES Positions (idPosition))

Comme vous pouvez le voir, la clé étrangère fait simplement référence à la même table que celle que nous créons. Sur le CD, dans le répertoire Chapter1, vous pouvez voir dans le fichier Foreign_keys_to_self.sql un exemple de création de cette table, la remplissant de données et affichant les positions en tenant compte de leur subordination. Dans le chapitre suivant, nous examinerons plus en détail la possibilité de travailler avec de telles tables.

Relation individuelle

Jusqu'à présent, nous avons examiné la relation classique, lorsqu'une ligne du tableau de données principal correspond à une ligne du tableau associé. Cette relation est appelée un-à-plusieurs. Mais il existe d'autres connexions, et nous allons maintenant en examiner une autre - une à une, lorsqu'un enregistrement de la table principale est connecté à l'enregistrement d'un autre. Pour mettre en œuvre cela, il suffit de lier les clés primaires des deux tables. Puisque les clés primaires ne peuvent pas être répétées, une seule ligne peut être liée dans les deux tables.

L'exemple suivant crée deux tables ayant une relation de clé primaire :

Une seule des tables a besoin d'une clé étrangère. Puisque la relation est un à un, peu importe dans quelle table la créer.

plusieurs à plusieurs

La relation la plus complexe est la relation plusieurs-à-plusieurs, dans laquelle de nombreux enregistrements d'une table correspondent à plusieurs enregistrements d'une autre table. Pour mettre en œuvre cela, deux tables ne suffisent pas, il faut trois tables.

Nous devons d’abord comprendre quand une relation plusieurs-à-plusieurs peut être utilisée ? Supposons que vous ayez deux tableaux : une liste des résidents de la maison et une liste de numéros de téléphone. Un appartement peut avoir plus d'un numéro, ce qui signifie qu'un nom de famille peut avoir deux numéros de téléphone. Il s’avère qu’il existe une relation un-à-plusieurs. En revanche, il peut y avoir deux familles dans un même appartement (un appartement commun ou simplement un locataire qui utilise le téléphone du propriétaire), ce qui signifie que la connexion entre le téléphone et le résident est également une à plusieurs. Et l'option la plus difficile est d'avoir deux téléphones dans un appartement commun. Dans ce cas, les deux numéros sont utilisés par plusieurs résidents de l'appartement. Il s’avère donc que « plusieurs » familles peuvent utiliser « plusieurs » téléphones (communication plusieurs-à-plusieurs).

Comment mettre en œuvre une relation plusieurs-à-plusieurs ? À première vue, cela est impossible dans le modèle relationnel. Il y a environ 10 ans, j'ai passé beaucoup de temps à chercher différentes options et j'ai donc simplement créé un tableau rempli de données redondantes. Mais un jour, on m'a confié une tâche, grâce à laquelle une excellente solution a émergé des conditions : je devais créer deux tables de résidents d'appartements et de numéros de téléphone et n'y implémenter qu'une clé primaire. Les clés étrangères ne sont pas nécessaires dans ce tableau. Mais la connexion entre les tables doit se faire via une troisième table de connexion. À première vue, cela est difficile et peu clair, mais une fois que vous aurez compris cette méthode, vous verrez toute la puissance de cette solution.

Les tableaux 1.6 et 1.7 montrent respectivement des exemples de tables de noms de famille et de numéros de téléphone. Et le tableau 1.8 montre le tableau de liaison.

Tableau 1.6. Tableau des noms de famille

Tableau 1.7. Table téléphonique

Tableau 1.8. Table téléphonique

Voyons maintenant à quoi ressemblera la logique de recherche de données dans une relation plusieurs-à-plusieurs. Disons que nous devons retrouver tous les téléphones appartenant à Ivanov. La clé primaire d'Ivanov est égale à 1. On retrouve dans la table de liaison tous les enregistrements pour lesquels le champ « Relation avec le nom » est égal à 1. Ce seront les enregistrements 1 et 2. Dans ces enregistrements dans le champ « Relation avec le téléphone » il y a sont les identifiants 1 et 2, respectivement, et cela signifie qu'Ivanov possède les numéros de la table téléphonique, qui se trouvent sur les lignes 1 et 2.

Résolvons maintenant le problème inverse - déterminons qui a accès au numéro de téléphone 567575677. Ce numéro dans la table téléphonique a la clé 3. Nous recherchons tous les enregistrements dans la table de liaison, où dans le champ « Connexion téléphonique » il est égal à 3. Il s'agit d'enregistrements portant les numéros 4 et 5, qui dans le champ "Nom Lien" contiennent respectivement les valeurs 2 et 3. Si vous regardez maintenant le tableau des noms de famille, vous verrez Petrov et Sidorov aux numéros 2 et 3. Cela signifie que ces deux résidents utilisent le numéro de téléphone 567575677.

Examinez les trois tableaux et assurez-vous de bien comprendre quels numéros de téléphone appartiennent à quels résidents et vice versa. Si vous voyez cette connexion, vous comprendrez que c'est aussi simple que trois centimes et vous pourrez rapidement la mettre en œuvre dans vos projets.

CREATE TABLE Noms (idName uniqueidentifier DEFAULT NEWID(), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName)) CREATE TABLE Phones (idPhone uniqueidentifier DEFAULT NEWID(), vcPhone varchar(10), CONSTRAINT PK_idPhone PRIMARY KEY (idPhone)) CREATE TABLE LinkTable (idLinkTable uniqueidentifier DEFAULT NEWID(), idName uniqueidentifier, idPhone uniqueidentifier, CONSTRAINT PK_idLinkTable CLÉ PRIMAIRE (idLinkTable), CONTRAINTE FK_idPhone CLÉ ÉTRANGÈRE (idPhone) RÉFÉRENCES Téléphones (idPhone), CONTRAINTE FK_idName CLÉ ÉTRANGÈRE (idName) RÉFÉRENCES Noms (idName) ) )

La table de liaison comporte deux clés étrangères qui renvoient aux tables de noms et de téléphones et une clé primaire qui garantit l'unicité des enregistrements.

J'ai choisi le champ GUID comme clé primaire car il est plus pratique pour résoudre ce problème particulier. Le fait est que nous devons insérer des enregistrements dans deux tables et dans les deux cas, nous devons spécifier la même clé. La valeur GUID peut être générée puis utilisée lors de l'insertion de données dans les deux tables.

Vous pouvez également utiliser un champ augmentant automatiquement comme clé, mais dans ce cas, le problème est un peu plus difficile à résoudre, ou plutôt, il n'est pas pratique de résoudre le problème. Par exemple, lors de l'ajout d'un numéro de téléphone, vous devez d'abord insérer la ligne correspondante dans le tableau, puis la rechercher, déterminer la clé qui a été attribuée à la ligne, puis établir la connexion.

À ce stade, nous sommes limités à la simple création de tableaux, mais dans la section 2.8, nous reviendrons sur ce sujet et apprendrons et apprendrons à travailler avec des tableaux associés. Travailler avec des relations un-à-un et un-à-plusieurs n'est pas très différent, car seules deux tables sont impliquées dans ce schéma. Les relations plusieurs-à-plusieurs sont un peu plus compliquées à cause de la table de liaison, nous aborderons donc cela séparément dans la section 2.27.

Les clés sont des éléments fondamentaux d'une base de données relationnelle car elles établissent une relation entre une paire de tables et fournissent une identification unique pour chaque enregistrement de la table. Les clés sont plus importantes que l’établissement de relations ; ils contribuent également à l’intégrité référentielle et constituent un élément majeur de l’intégrité au niveau de la table. Les tables stockent d’énormes morceaux de données qui couvrent généralement des milliers d’enregistrements, tous non triés et désorganisés. Récupérer des données spécifiques à partir de ces multiples enregistrements peut parfois être difficile, voire impossible. C'est ici que les clés apparaissent. Ici, nous examinerons deux clés de schéma de base de données relationnelles très importantes et la différence entre elles : la clé primaire et la clé étrangère.

Qu'est-ce qu'une clé primaire ?

Une clé primaire est une clé spéciale qui identifie de manière unique chaque enregistrement d'une table. Dans une base de données relationnelle, il est très important d'avoir un identifiant unique dans chaque ligne d'une table, et une clé primaire est simplement ce dont vous avez besoin pour identifier de manière unique un tuple dans une table. Un tuple est une collection d'attributs de valeur dans une base de données relationnelle. Une clé primaire peut faire référence à une colonne ou à un ensemble de colonnes dans une table de base de données relationnelle utilisée pour identifier implicitement tous les enregistrements de la table. La clé primaire doit être unique pour chaque enregistrement car elle agit comme un identifiant unique et ne doit pas contenir de valeurs Null. Chaque base de données doit avoir une et une seule clé primaire.

Qu'est-ce qu'une clé étrangère ?

Une clé étrangère fait référence à un champ ou à une collection de champs dans un enregistrement de base de données qui identifie de manière unique le champ clé d'un autre enregistrement de base de données dans une autre table. En termes simples, il établit une relation entre les enregistrements de deux tables différentes d'une base de données. Il peut s'agir d'une colonne d'une table qui pointe vers les colonnes de clé primaire, ce qui signifie que la clé étrangère définie dans la table fait référence à la clé primaire d'une autre table. Les liens sont essentiels dans les bases de données relationnelles pour établir les relations entre les enregistrements, nécessaires au tri des bases de données. Les clés étrangères jouent un rôle important dans la normalisation des bases de données relationnelles, en particulier lorsque les tables doivent accéder à d'autres tables.

Différence entre clé primaire et clé étrangère

Notions de base de la clé primaire et de la clé étrangère

Une clé primaire est une clé spéciale dans une base de données relationnelle qui agit comme un identifiant unique pour chaque enregistrement, ce qui signifie qu'elle identifie de manière unique chaque ligne/enregistrement d'une table et que sa valeur doit être unique pour chaque ligne de la table. D'un autre côté, une clé étrangère est un champ d'une table qui relie deux tables entre elles. Il fait référence à une colonne ou à un groupe de colonnes qui identifie de manière unique une ligne d'une autre table ou de la même table.

Relation clé primaire et clé étrangère

Une clé primaire identifie de manière unique un enregistrement dans une table de base de données relationnelle, tandis qu'une clé étrangère fait référence à un champ d'une table qui est la clé primaire d'une autre table. La clé primaire doit être unique et une seule clé primaire est autorisée dans une table à définir, alors que plusieurs clés étrangères sont autorisées dans une table.

Clé primaire et valeurs de clé étrangère en double

Une clé primaire est une combinaison de contraintes UNIQUE et Not Null, donc un champ de clé primaire dans une table de base de données relationnelle ne peut pas avoir de valeurs en double. Deux lignes ne peuvent pas contenir de valeurs en double pour un attribut de clé primaire. Contrairement à une clé primaire, une clé étrangère peut contenir des valeurs en double et une table dans une base de données relationnelle peut contenir plusieurs clés étrangères.

Clé primaire et clé étrangère NULL

L’une des principales différences entre les deux est que, contrairement aux clés primaires, les clés étrangères peuvent également contenir des valeurs NULL. Une table dans une base de données relationnelle ne peut avoir qu'une seule clé primaire, qui n'est pas nullable.

Table temporaire de clé primaire et clé étrangère

Une contrainte de clé primaire peut être définie implicitement sur les tables temporaires et leurs variables, alors qu'une contrainte de clé étrangère ne peut pas être appliquée aux tables temporaires locales ou globales.

Supprimer une clé primaire et une clé étrangère

Une valeur de clé primaire ne peut pas être supprimée d'une table parent appelée clé étrangère dans une table enfant. Avant de pouvoir supprimer une table parent, vous devez d'abord supprimer la table enfant. En revanche, une valeur de clé étrangère peut être supprimée d'une table enfant même si la valeur appartient à la clé primaire de la table parent.

Clé primaire ou clé étrangère : tableau de comparaison

Résumé des clés principales

Les clés jouent un rôle crucial dans l'existence d'un schéma de base de données pour établir des relations entre les tables et au sein d'une table. Les clés établissent des relations et appliquent divers types d'intégrité, en particulier l'intégrité au niveau des tables et des relations. Premièrement, ils pensent qu’une table contient des enregistrements uniques et que les champs que vous utilisez pour établir des relations entre les tables doivent contenir les valeurs correspondantes. La clé primaire et la clé étrangère sont les deux types de clés les plus importants et les plus courants utilisés dans les bases de données relationnelles. Une clé primaire est une clé spéciale utilisée pour identifier de manière unique les enregistrements d'une table, tandis qu'une clé étrangère est utilisée pour établir une relation entre deux tables. Les deux ont une structure identique mais jouent des rôles différents dans un schéma de base de données relationnelle.

La figure montre un tableau (un rapport de degré 5) contenant des informations sur les employés d'une entreprise hypothétique. Les lignes du tableau correspondent à des tuples. Chaque ligne est en fait une description d'un objet du monde réel (dans ce cas, un travailleur), dont les caractéristiques sont contenues dans les colonnes. Les relations relationnelles correspondent à des ensembles d'entités et les tuples correspondent à des entités. Les colonnes d'un tableau représentant une relation relationnelle sont appelées les attributs.

Chaque attribut est défini sur un domaine, un domaine peut donc être considéré comme l'ensemble des valeurs valides pour un attribut donné. Plusieurs attributs d'une même relation, et même des attributs de relations différentes, peuvent être définis sur le même domaine.

Un attribut dont la valeur identifie de manière unique les tuples est appelé clé (ou simplement clé). La clé est l'attribut « Numéro de personnel », puisque sa valeur est unique pour chaque salarié de l'entreprise. Si les tuples sont identifiés uniquement en concaténant les valeurs de plusieurs attributs, alors la relation est dite avoir une clé composite.

Clé primaire- dans un modèle de données relationnel, une des clés potentielles d'une relation, sélectionnée comme clé primaire (ou clé par défaut).

Une relation peut contenir plusieurs clés. Une des clés est toujours déclarée primaire, ses valeurs ne peuvent pas être mises à jour. Toutes les autres clés de relation sont appelées clés possibles.

D'un point de vue théorique, toutes les clés de relation potentielles (possibles) sont équivalentes, c'est-à-dire qu'elles ont les mêmes propriétés d'unicité et de minimalité. Cependant, la clé primaire est généralement sélectionnée parmi les clés potentielles les plus pratiques à certaines fins pratiques, par exemple pour créer externe clés à d’autres égards ou pour créer un index clusterisé. Par conséquent, en règle générale, celle qui a la plus petite taille (stockage physique) et/ou comprend le plus petit nombre d’attributs est choisie comme clé primaire.

Si clé primaire se compose d’un seul attribut, on l’appelle avec une simple clé.

Si clé primaire se compose de deux ou plusieurs attributs, on l'appelle clé composée. Ainsi, le prénom, le nom, le patronyme, le numéro de passeport, la série de passeport ne peuvent pas être des clés primaires individuellement, puisqu'ils peuvent être les mêmes pour deux ou plusieurs personnes. Mais il n’existe pas deux documents personnels du même type portant la même série et le même numéro. Par conséquent, dans une relation contenant des données sur des personnes, la clé primaire peut être un sous-ensemble d'attributs constitué du type de document personnel, de sa série et de son numéro.

Contrairement aux modèles de données hiérarchiques et en réseau, le modèle relationnel n'a pas la notion de relation de groupe. Pour refléter les associations entre des tuples de relations différentes, la duplication de leurs clés est utilisée.

Les attributs qui sont des copies de clés d'autres relations sont appelés clés étrangères.

Par exemple, la relation entre les relations DEPARTMENT et EMPLOYEE est créée en copiant la clé primaire "Numéro_departement" de la première relation à la seconde. Ainsi, afin d'obtenir une liste des employés d'un service donné, il faut : 1) Depuis la table DEPARTMENT, définir la valeur de l'attribut "Numéro_departement" , correspondant à ce « Department_Name ». 2) sélectionnez tous les enregistrements de la table EMPLOYEE, valeur d'attribut "Numéro_departement" qui est égale à celle obtenue à l’étape précédente. Afin de savoir dans quel service travaille un salarié, vous devez effectuer l'opération inverse : 1) Déterminer "Numéro_departement" de la table EMPLOYÉ. 2) A l'aide de la valeur obtenue, on retrouve l'entrée dans la table DEPARTMENT.

18. Normalisation dans les bases de données relationnelles, concept de forme normale dans la conception de bases de données.

Forme normale - une propriété d'une relation dans un modèle de données relationnel, la caractérisant du point de vue de la redondance, ce qui peut potentiellement conduire à des résultats logiquement erronés d'échantillonnage ou de modification des données. La forme normale est définie comme un ensemble d'exigences auxquelles une relation doit satisfaire.

Le processus de conversion d'une base de données sous sa forme normale est appelé normalisation . La normalisation vise à amener la structure de la base de données sous une forme offrant une redondance minimale, c'est-à-dire qu'elle n'a pas pour but de réduire ou d'augmenter la productivité du travail ni de réduire ou d'augmenter le volume de la base de données. Le but ultime de la normalisation est de réduire l'incohérence potentielle des informations stockées dans la base de données.

L'élimination de la redondance s'effectue, en règle générale, en décomposant les relations de telle sorte que seuls les faits primaires soient stockés dans chaque relation (c'est-à-dire les faits qui ne sont pas déduits d'autres faits stockés).

Dépendances fonctionnelles.

Une base de données relationnelle contient des informations à la fois structurelles et sémantiques. La structure d'une base de données est déterminée par le nombre et le type de relations qu'elle contient, ainsi que par les relations un-à-plusieurs qui existent entre les tuples de ces relations. La partie sémantique décrit l'ensemble des dépendances fonctionnelles qui existent entre les attributs de ces relations. Définissons la dépendance fonctionnelle.

19. 1NF : Définitions de base et règles de transformation.

Pour discuter de la première forme normale, deux définitions sont nécessaires :

Attribut simple - un attribut dont les valeurs sont atomiques (indivisibles).

Attribut complexe - est obtenu en connectant plusieurs attributs atomiques pouvant être définis sur le même domaine ou sur des domaines différents (on l'appelle aussi vecteur ou agrégat de données).

Définition de la première forme normale :

une relation est en 1NF si les valeurs de tous ses attributs sont atomiques. . Sinon, ce n'est pas du tout une table et ces attributs doivent être décomposés.

Regardons un exemple :

Dans la base de données du service RH de l'entreprise, il est nécessaire de stocker des informations sur les salariés qu'on peut tenter de présenter en relation avec

EMPLOYÉ(EMPLOYEE_NUMBER, NOM, DATE DE NAISSANCE, WORK_HISTORY, ENFANTS).

D'un examen attentif de cette relation, il s'ensuit que les attributs "antécédents de travail" Et "enfants" sont complexes, de plus, l'attribut "antécédents de travail" inclut un autre attribut complexe "historique_salaire".
Ces unités ressemblent à ceci :

 JOB_HISTORY (RECEPTION_DATE, NOM, SALARY_HISTORY),

 SALARY_HISTORY (APPOINTMENT_DATE, SALAIRE),

 ENFANTS (CHILD_NAME, BIRTH_YEAR).

Leur connexion est représentée sur la Fig. 3.3.

Figure 3.3. Attitude initiale.

Pour amener la relation originelle SERVANT à la première forme normale, il faut la décomposer en quatre relations, comme le montre la figure suivante :

Figure 3.4. Ensemble normalisé de relations.

Ici, la clé primaire de chaque relation est mise en évidence par un cadre bleu, les noms des clés étrangères sont en police bleue. Rappelez-vous que les clés étrangères sont utilisées pour représenter les dépendances fonctionnelles qui existent dans la relation source. Ces dépendances fonctionnelles sont indiquées par des lignes avec des flèches.

L'algorithme de normalisation est décrit par E.F. Codd comme suit :

En commençant par la relation en haut de l'arborescence (Figure 3.3.), sa clé primaire est prise et chaque relation immédiatement subordonnée est développée en insérant un domaine ou une combinaison de domaines de cette clé primaire.
La Clé Primaire de chaque relation ainsi développée se compose de la Clé Primaire que la relation avait avant l'extension et de la Clé Primaire ajoutée de la relation parent.
Après cela, tous les domaines non simples sont supprimés de la relation parent, le nœud supérieur de l'arborescence est supprimé et la même procédure est répétée pour chacun des sous-arbres restants.

20. 2NF : définitions de base et les règles de transformation.

Très souvent, la clé primaire d'une relation comprend plusieurs attributs (auquel cas on l'appelle composite) - voir, par exemple, la relation ENFANTS présentée dans la Fig. 3.4 question 19. Parallèlement, le concept est introduit dépendance fonctionnelle totale.

Définition:

un attribut non-clé est fonctionnellement entièrement dépendant d'une clé composite s'il est fonctionnellement dépendant de la clé entière dans son ensemble, mais n'est fonctionnellement dépendant d'aucun de ses attributs constitutifs.

Exemple:

Soit une relation SUPPLY (N_SUPPLIER, PRODUCT, PRICE).
Un fournisseur peut fournir différents produits, et le même produit peut être fourni par différents fournisseurs. Alors la clé de relation est "N_fournisseur + produit". Laissez tous les fournisseurs fournir des biens au même prix. Nous avons alors les dépendances fonctionnelles suivantes :

N_fournisseur, produit -> prix
produit -> prix

La dépendance fonctionnelle incomplète de l'attribut prix sur la clé conduit à l'anomalie suivante : lorsque le prix d'un article change, une vue complète de la relation est nécessaire afin de modifier tous les enregistrements concernant ses fournisseurs. Cette anomalie est une conséquence du fait que deux faits sémantiques sont combinés dans une seule structure de données. Le développement suivant donne les relations en 2NF :

LIVRAISON (N_FOURNISSEUR, PRODUIT)
PRODUIT_PRICE (PRODUIT, PRIX)

Pour que tu puisses donner

Définition de la deuxième forme normale : Une relation est en 2NF si elle est en 1NF et chaque attribut non-clé dépend entièrement fonctionnellement de la clé.

21. 3NF : définitions de base et les règles de transformation.

Avant d’aborder la troisième forme normale, il est nécessaire d’introduire le concept : dépendance fonctionnelle transitive.

Définition:

Soit X, Y, Z trois attributs d'une relation. Dans ce cas, X --> Y et Y --> Z, mais il n'y a pas de correspondance inverse, c'est-à-dire Z -> Y et Y -> X. Alors Z dépend transitivement de X.
Soit une relation STOCKAGE ( FERME, ENTREPÔT, VOLUME), qui contient des informations sur les entreprises recevant des marchandises des entrepôts et les volumes de ces entrepôts. Attribut clé - "ferme". Si chaque entreprise ne peut recevoir des marchandises que d'un seul entrepôt, il existe à cet égard les dépendances fonctionnelles suivantes :

ferme -> action
action -> volume

Dans ce cas, des anomalies surviennent :

si pour le moment aucune entreprise ne reçoit de marchandises de l'entrepôt, les données sur son volume ne peuvent pas être saisies dans la base de données (puisque l'attribut clé n'est pas défini)
si le volume de l'entrepôt change, il est nécessaire de visualiser l'ensemble de la relation et de modifier les fiches de toutes les sociétés associées à cet entrepôt.

Pour éliminer ces anomalies, il faut décomposer la relation originelle en deux :

STOCKAGE ( FERME, ACTION)
STOCKAGE_VOLUME ( ACTION, VOLUME)

Définition de la troisième forme normale :

Une relation est en 3NF si elle est en 2NF et chaque attribut non-clé ne dépend pas transitivement de la clé primaire.

InterBase peut utiliser les types de restrictions suivants :

PRIMARY KEY - la clé primaire de la table.
UNIQUE - clé de table unique.
CLÉ ÉTRANGÈRE- clé étrangère, fournit un lien vers une autre table et garantit l'intégrité référentielle entre le parent et tables enfants.

Une note sur la terminologie

Si vous êtes comme l'auteur de ce cours en ce sens que vous aimez chercher des réponses à une question qui vous intéresse de manière globale, dans différents ouvrages de différents auteurs, alors vous ne pourrez pas vous empêcher de remarquer une certaine confusion dans les définitions. principal (maître) -> subordonné (détail) les tables. Rappelez-vous que la table principale est souvent appelée table parent et que la table subordonnée est souvent appelée table enfant.

Cela est probablement dû à la façon dont ces définitions sont interprétées dans les SGBD locaux et SQL Server.

Dans les SGBD locaux, la table principale est celle qui contient les données principales et la table subordonnée contient des données supplémentaires. Prenons, par exemple, trois tables liées. Le premier contient des données sur les ventes, le second - sur les produits et le troisième - sur les clients :

Riz. 18.1.

Ici, les informations principales sont stockées dans la table des ventes, il s'agit donc de la table principale (parente). Des informations supplémentaires sont stockées dans les tables produit et client, ce qui signifie qu'il s'agit d'enfants. C'est compréhensible : une fille ne peut pas avoir deux mères biologiques, mais une mère est tout à fait capable de donner naissance à deux filles.

Mais dans les serveurs de bases de données SQL, il existe une définition différente des relations : lorsqu'un champ d'une table fait référence à un champ d'une autre table, on l'appelle clé étrangère. Et le champ auquel il fait référence s'appelle parent ou clé primaire. Une table qui possède une clé étrangère (un lien vers un enregistrement dans une autre table) est souvent appelée enfant, et une table avec clé parente- parental. Également dans la définition des relations, ils disent qu'un parent ne peut avoir qu'un seul enregistrement unique, qui peut être référencé par plusieurs enregistrements. table enfant.

Ainsi, dans l'exemple ci-dessus, la table sales possède deux clés étrangères : l'ID produit et l'ID client. Et les deux tableaux du côté droit de la figure ont clé parente"Identifiant". Étant donné que le même client ou produit peut apparaître à plusieurs reprises dans le tableau des ventes, il s'avère que les deux tableaux de droite de la figure sont des parents et que le tableau de gauche est un enfant. Parce que nous étudions maintenant InterBase-SQL serveur de base de données, nous serons guidés par ces définitions dans les cours ultérieurs. Pour ne pas nous creuser davantage la tête avec cette confusion, mettons-nous d’emblée d’accord : table enfant a une clé étrangère (FOREIGN KEY) vers une autre table.

CLÉ PRIMAIRE

CLÉ PRIMAIRE- la clé primaire est l'un des principaux types de restrictions dans une base de données. La clé primaire est conçue pour identifier de manière unique un enregistrement dans une table et doit être unique. Les clés primaires PRIMARY KEY se trouvent dans des tables, généralement appelées parent (Parent). La clé primaire ne doit pas être confondue avec les index primaires des bases de données locales ; la clé primaire n'est pas un index, mais une contrainte. Lors de la création d'une clé primaire InterBase crée automatiquement pour lui indice unique. Cependant, si l'on crée indice unique, cela ne créera pas contraintes de clé primaire. Une table ne peut avoir qu’une seule clé primaire, PRIMARY KEY.

Disons que vous avez un tableau avec une liste d'employés. Le champ Nom de famille peut contenir des valeurs en double (homonymes), il ne peut donc pas être utilisé comme clé primaire. C'est rare, mais il existe des homonymes qui, en plus, portent les mêmes noms. Encore plus rarement, il existe des homonymes complets, de sorte que même les trois champs « Nom » + « Prénom » + « Patronyme » ne peuvent pas garantir l'unicité de l'enregistrement et ne peuvent pas être la clé primaire. Dans ce cas, la solution, comme précédemment, est d'ajouter un champ identifiant qui contient le numéro de série de cette personne. De tels champs sont généralement auto-incrémentés (nous parlerons de l'organisation des champs auto-incrémentés dans les prochains cours). Donc,

Clé primaire représente un ou plusieurs champs d'une table dont la combinaison est unique pour chaque enregistrement.

Si la clé primaire contient une seule colonne (comme c'est le plus souvent le cas), le spécificateur PRIMARY KEY est utilisé lorsque définition de colonne:

CREATE TABLE Prim_1(Stolbec1 INT NON CLÉ PRIMAIRE NULL, Stolbec2 VARCHAR(50))

Si la clé primaire est construite sur plusieurs colonnes, alors le spécificateur est placé après avoir défini tous les champs :

CREATE TABLE Prim_2(Stolbec1 INT NON NULL, Stolbec2 VARCHAR(50) NON NULL, CLÉ PRIMAIRE (Stolbec1, Stolbec2))

Comme le montrent les exemples, la clé primaire doit avoir une contrainte de colonne(s) NOT NULL.

UNIQUE

UNIQUE- clé unique. Le spécificateur UNIQUE indique que toutes les valeurs de ce champ doivent être uniques ; par conséquent, ces champs ne peuvent pas non plus contenir de valeurs. NUL. On pourrait dire qu'une clé UNIQUE est une alternative à une clé primaire, mais il existe des différences. La principale différence est qu’il ne doit y avoir qu’une seule clé primaire, alors qu’il peut y avoir plusieurs clés uniques. De plus, une contrainte UNIQUE ne peut pas être créée sur le même ensemble de colonnes que celui utilisé pour une PRIMARY KEY ou une autre contrainte UNIQUE. Les clés uniques, comme les clés primaires, se trouvent dans les tables qui sont les parents d'autres tables.

Une colonne déclarée avec une contrainte UNIQUE, comme une clé primaire, peut être utilisée pour renforcer l'intégrité référentielle entre son parent et tables enfants. Dans ce cas, la clé étrangère table enfant fera référence à ce(s) champ(s). Comme pour une clé primaire, lorsqu'une clé unique est créée, un indice unique. Mais pas l’inverse. Un exemple de création d'une table avec une clé primaire et deux clés uniques :

CREATE TABLE Prim_3(Stolbec1 INT NOT NULL PRIMARY KEY, Stolbec2 VARCHAR(50) NOT NULL UNIQUE, Stolbec3 FLOAT NOT NULL UNIQUE)

CLÉ ÉTRANGÈRE

CLÉ ÉTRANGÈRE- clé externe . Il s'agit d'un outil très puissant pour garantir l'intégrité référentielle entre les tables, qui permet non seulement de surveiller la présence des liens corrects, mais aussi de les gérer automatiquement. Les clés étrangères sont contenues dans des tables qui sont des enfants (Child) d'autres tables. Intégrité référentielle est fourni précisément par une clé étrangère qui fait référence à la clé primaire ou

Site sur l'informatique