Comment ajouter une contrainte de clé étrangère à la table A (id, type) référençant l'une des deux tables Table B (id, type) ou Table C (id, type)?

vous ne pouvez ajouter qu'une seule contrainte pour une colonne ou une paire de colonnes. Pensez aux pommes et aux oranges. Une colonne ne peut pas faire référence à la fois aux oranges et aux pommes. Il doit être orange ou pomme.

En remarque, ceci peut être réalisé d'une manière ou d'une autre avec des colonnes PERSISTED COMPUTED , mais cela n'introduit que des frais généraux et de la complexité.

Vérifiez ceci pour référence

Je pense qu'il y a une faille dans la conception de votre base de données. La meilleure façon de résoudre votre problème réel est de regrouper les recettes et les produits dans un seul tableau. À l'heure actuelle, vous avez une colonne redondante dans chaque table appelée item_type_id. Cette colonne ne vaut rien, sauf si vous avez réellement les éléments dans la même table. Je dis redondant, car il a la même valeur pour absolument toutes les entrées de chaque table.

Vous avez deux options. Si vous ne pouvez pas modifier la conception de la base de données, travaillez sans clés étrangères et sélectionnez la couche logique parmi les tables correctes.

Ou, si vous pouvez modifier la conception de la base de données, faites en sorte que les produits et les recettes existent dans la même table. Vous avez déjà une table item_type, qui peut identifier la catégorisation des articles, il est donc logique de mettre tous les articles dans la même table

Vous pouvez ajouter des colonnes calculées à la table Inventory :

ALTER TABLE Inventory
    ADD _recipe_item_id AS CASE WHEN item_type_id = 1 THEN item_id END persisted
ALTER TABLE Inventory
    ADD _product_item_id AS CASE WHEN item_type_id = 2 THEN item_id END persisted

Vous pouvez ensuite ajouter deux clés étrangères distinctes aux deux tables, en utilisant ces deux colonnes à la place sur item_id . Je suppose que la colonne item_type_id dans ces deux tables est déjà calculée / contrainte de manière appropriée, mais sinon, vous voudrez peut-être en tenir compte également.

Parce que ces colonnes calculées sont NULL lorsque le mauvais type est sélectionné, et parce que SQL Server ne vérifie pas les contraintes FK si au moins une valeur de colonne est NULL , elles peuvent exister et seulement l'une ou l'autre le sera satisfait à tout moment.

Étant donné que vos produits et vos recettes sont stockés séparément et semblent avoir pour la plupart des colonnes séparées, des tableaux d'inventaire séparés sont probablement la bonne approche. par exemple

CREATE TABLE dbo.Products
(
        item_id BIGINT NOT NULL,
        Item_type_id AS 2,
        name VARCHAR(50) NOT NULL,
        Price DECIMAL(10, 4) NOT NULL,
        InStock BIT NOT NULL,
    CONSTRAINT PK_Products__ItemID PRIMARY KEY (item_id),
    CONSTRAINT FK_Products__Item_Type_ID FOREIGN KEY (Item_Type_ID) 
        REFERENCES Item_Type (Item_Type_ID),
    CONSTRAINT FK_Products__ItemID_ItemTypeID FOREIGN KEY (item_id, Item_Type_ID) 
        REFERENCES dbo.Item (item_id, item_type_id)
);

Si vous avez besoin de tous les types combinés, vous pouvez simplement utiliser une vue:

CREATE TABLE dbo.Items
(
        item_id INT IDENTITY(1, 1) NOT NULL 
        Item_type_id INT NOT NULL,
    CONSTRAINT PK_Items__ItemID PRIMARY KEY (item_id),
    CONSTRAINT FK_Items__Item_Type_ID FOREIGN KEY (Item_Type_ID) REFERENCES Item_Type (Item_Type_ID),
    CONSTRAINT UQ_Items__ItemID_ItemTypeID UNIQUE (Item_ID, Item_type_id)
);

SI vous créez un nouveau type d'élément, alors vous besoin de modifier la conception de la base de données de toute façon pour créer une nouvelle table, il vous suffirait donc de modifier la vue en même temps

Une autre possibilité, serait d'avoir une seule table Items, puis d'avoir Products / Les recettes font référence à cela. Vous commencez donc par votre table items, dont chacun a un identifiant unique:

CREATE VIEW dbo.Inventory
AS
    SELECT  Product_id AS item_id,
            2 AS item_type_id,
            [Count]
    FROM    ProductInventory
    UNION ALL
    SELECT  recipe_id AS item_id,
            1 AS item_type_id
            [Count]
    FROM    RecipeInventory;
GO

Notez la clé unique ajoutée sur (item_id, item_type_id) , ceci est important pour l'intégrité référentielle plus tard.

Ensuite, chacune de vos sous-tables a une relation 1: 1 avec ceci, donc votre table de produits deviendrait:

CREATE TABLE dbo.ProductInventory
(
        Product_id INT NOT NULL,
        [count] INT NOT NULL,
    CONSTRAINT FK_ProductInventory__Product_id FOREIGN KEY (Product_id) 
        REFERENCES dbo.Product (Product_id)
);

CREATE TABLE dbo.RecipeInventory
(
        Recipe_id INT NOT NULL,
        [count] INT NOT NULL,
    CONSTRAINT FK_RecipeInventory__Recipe_id FOREIGN KEY (Recipe_id) 
        REFERENCES dbo.Recipe (Recipe_id )
);

Quelques points à noter:

• item_id est à nouveau la clé primaire, garantissant la relation 1: 1.
• la colonne calculée item_type_id (comme 2) garantissant que tous les item_type_id sont mis à 2. Il s'agit de la clé car elle permet d'ajouter une contrainte de clé étrangère
• la clé étrangère sur (item_id, item_type_id) retour à la table items. Cela garantit que vous ne pouvez insérer un enregistrement dans la table des produits que si l'enregistrement d'origine dans la table des articles a un item_type_id de 2.

Une troisième option serait une table unique pour les recettes et les produits et rendrait toutes les colonnes non requises pour les deux nullables. Cette réponse sur les types d'héritage vaut la peine d'être lue.