Comment gérer plusieurs références de clé étrangère à une seule colonne dans la conception d'une base de données ?

Références de clés étrangères multiples à une seule colonne

Dans la conception de bases de données, il peut être avantageux de référencer une seule colonne dans une table comme étrangère clés dans plusieurs autres tables. Cette approche permet une association transparente des données et une récupération cohérente des données sur plusieurs entités.

Énoncé du problème

Considérez le scénario de base de données suivant :

Vous avez une table appelé pdf_created avec les colonnes item_type, item_id et quantité. Chaque ligne de ce tableau représente un élément extrait d'un document PDF. La colonne item_type identifie le type d'article (par exemple, "1" pour le produit, "2" pour l'en-tête du modèle). La colonne item_id fait référence à la clé primaire de la table d'éléments correspondante (par exemple, produit, service, en-tête). L'objectif est de référencer la colonne item_id en tant que clé étrangère dans les trois tables d'éléments : produit, service et en-tête.

Cependant, le code SQL suivant ne parvient pas à créer les relations souhaitées :

CREATE TABLE `pdf_created` (
    `id` INT(10) UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
    `pdf_id` INT(10) NOT NULL,
    `item_type` INT(3) UNSIGNED NOT NULL,
    `item_id` INT(10) UNSIGNED NOT NULL,
    `quantity` INT(3) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `FK_pdf_id` (`pdf_id`),
    CONSTRAINT `FK_pdf_id` FOREIGN KEY (`pdf_id`) REFERENCES `pdf` (`id`),
    KEY `FK_item_type` (`item_type`),
    CONSTRAINT `FK_item_type` FOREIGN KEY (`item_type`) REFERENCES `item` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    KEY `FK_item_id` (`item_id`),
    CONSTRAINT `FK_item_id` FOREIGN KEY (`item_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK_item_id` FOREIGN KEY (`item_id`) REFERENCES `service` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK_item_id` FOREIGN KEY (`item_id`) REFERENCES `header` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Raison de l'échec

Le problème réside dans les multiples contraintes de clé étrangère sur la colonne item_id. Une contrainte de clé étrangère garantit que chaque valeur de la colonne item_id est présente en tant que valeur de clé primaire dans au moins l'une des quatre tables (produit, service, en-tête). Cependant, il est impossible de garantir que item_id existera toujours en tant que clé primaire dans les quatre tables simultanément.

Solution

Bien qu'il ne soit pas possible de créer plusieurs clés étrangères contraintes clés sur une seule colonne faisant référence à différentes tables, vous pouvez toujours utiliser la colonne item_id pour référencer les trois tables d'éléments. Supprimez toutes les contraintes de clé étrangère sur la colonne item_id et comptez sur le code de l'application pour appliquer l'intégrité référentielle :

CREATE TABLE `pdf_created` (
    `id` INT(10) UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
    `pdf_id` INT(10) NOT NULL,
    `item_type` INT(3) UNSIGNED NOT NULL,
    `item_id` INT(10) UNSIGNED NOT NULL,
    `quantity` INT(3) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `FK_pdf_id` (`pdf_id`),
    CONSTRAINT `FK_pdf_id` FOREIGN KEY (`pdf_id`) REFERENCES `pdf` (`id`),
    KEY `FK_item_type` (`item_type`),
    CONSTRAINT `FK_item_type` FOREIGN KEY (`item_type`) REFERENCES `item` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Cela vous permet de maintenir les relations de données nécessaires sans introduire d'erreurs d'intégrité des données.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!