Clé étrangère vers une clé non primaire

Question

Je dispose d'une table qui contient des données, et l'une de ces lignes doit exister dans une autre table. Donc, je veux une clé étrangère pour maintenir l'intégrité référentielle.

CREATE TABLE table1
(
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   AnotherID INT NOT NULL,
   SomeData VARCHAR(100) NOT NULL
)

CREATE TABLE table2
(
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   AnotherID INT NOT NULL,
   MoreData VARCHAR(30) NOT NULL,

   CONSTRAINT fk_table2_table1 FOREIGN KEY (AnotherID) REFERENCES table1 (AnotherID)
)

Cependant, comme vous pouvez le voir, dans la table à laquelle je fais référence avec la clé étrangère, la colonne n'est pas la PK. Existe-t-il un moyen de créer cette clé étrangère, ou peut-être une meilleure façon de maintenir cette intégrité référentielle ?

Answer 1

Si vous voulez vraiment créer une clé étrangère vers une clé non primaire, elle DOIT être une colonne qui a une contrainte d'unicité dessus.

De Books Online:

Une contrainte de clé étrangère n'a pas besoin d'être liée uniquement à une contrainte PRIMARY KEY dans une autre table ; elle peut également être définie pour référencer les colonnes d'une contrainte UNIQUE dans une autre table.

Donc dans votre cas, si vous rendez AnotherID unique, cela sera autorisé. Si vous ne pouvez pas appliquer de contrainte d'unicité, vous êtes malchanceux, mais cela a vraiment du sens si vous y réfléchissez.

Cependant, comme cela a été mentionné, si vous avez une clé primaire parfaitement valide comme clé candidate, pourquoi ne pas l'utiliser ?

Answer 2

Comme d'autres l'ont souligné, idéalement, la clé étrangère serait créée comme une référence à une clé primaire (généralement une colonne IDENTITY). Cependant, nous ne vivons pas dans un monde idéal, et parfois même un "petit" changement à un schéma peut avoir des effets significatifs sur la logique de l'application.

Considérez le cas d'une table Client avec une colonne SSN (et une clé primaire stupide), et une table Réclamation qui contient également une colonne SSN (remplie par la logique commerciale à partir des données Client, mais aucune clé étrangère n'existe). La conception est défectueuse, mais est utilisée depuis plusieurs années, et trois applications différentes ont été construites sur le schéma. Il devrait être évident que supprimer Réclamation.SSN et mettre en place une véritable relation PK-FK serait idéal, mais ce serait aussi une refonte significative. D'un autre côté, mettre une contrainte UNIQUE sur Client.SSN, et ajouter une clé étrangère sur Réclamation.SSN, pourrait fournir une intégrité référentielle, avec peu ou pas d'impact sur les applications.

Ne vous méprenez pas, je suis tout à fait pour la normalisation, mais parfois le pragmatisme l'emporte sur l'idéalisme. Si une conception médiocre peut être aidée avec un pansement, la chirurgie pourrait être évitée.

Answer 3

Nécromancie.
Je suppose que lorsque quelqu'un atterrit ici, il a besoin d'une clé étrangère pour une colonne dans une table contenant des clés non uniques.

Le problème est que si vous avez ce problème, le schéma de la base de données est dénormalisé.

Vous gardez par exemple les chambres dans une table, avec une clé primaire room-uid, un champ DateFrom et un champ DateTo, et une autre uid, ici RM_ApertureID pour suivre la même chambre, et un champ de suppression souple, comme RM_Status, où 99 signifie 'supprimé', et <> 99 signifie 'actif'.

Donc, lorsque vous créez la première chambre, vous insérez RM_UID et RM_ApertureID avec la même valeur que RM_UID. Ensuite, lorsque vous terminez la chambre à une date, et que vous la rétablissez avec une nouvelle plage de dates, RM_UID est newid(), et le RM_ApertureID de l'entrée précédente devient le nouveau RM_ApertureID.

Donc, dans ce cas, si RM_ApertureID est un champ non-unique, vous ne pouvez pas définir de clé étrangère dans une autre table.

Et il n'y a aucun moyen de définir une clé étrangère sur une colonne / index non unique, par exemple dans T_ZO_REM_AP_Raum_Reinigung (où RM_UID est en réalité RM_ApertureID).
Mais pour interdire les valeurs invalides, vous devez définir une clé étrangère, sinon, les données seront corrompues tôt ou tard...

Maintenant, ce que vous pouvez faire dans ce cas (à part réécrire toute l'application) est d'insérer une contrainte de VÉRIFICATION, avec une fonction scalaire vérifiant la présence de la clé :

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ZO_REM_AP_Raum_Reinigung]'))
ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung DROP CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]
GO

... (le reste du code pourrait également être traduit en français)

Answer 4

Les clés primaires doivent toujours être uniques, les clés étrangères doivent permettre des valeurs non uniques si la table est une relation un-à-plusieurs. Il est parfaitement acceptable d'utiliser une clé étrangère comme clé primaire si la table est liée par une relation un-à-un, et non une relation un-à-plusieurs.

Une contrainte DE CLÉ ETRANGÈRE n'a pas besoin d'être liée uniquement à une contrainte DE CLÉ PRIMAIRE dans une autre table; elle peut également être définie pour référencer les colonnes d'une contrainte UNIQUE dans une autre table.

Answer 5

Oui, vous devriez généralement au moins l'indexer.

créer table étudiant(
    id int,
    nom varchar(30),
    index inName(id)
);

CRÉER TABLE note(
    id int,
    matière varchar(30),
    note double,
    clé étrangère(id) références étudiant(id)
);