Comment faire référence à d'autres tables dans la vérification des contraintes?

J'ai une table, ProductSupportArticles:

ProductSupportArticleID int NOT NULL <primary key>
ParentArticleID int NULL
ProductID int NOT NULL
Title varchar(100) NOT NULL
Content varchar(MAX) NOT NULL

ProductID est une clé étrangère de Produits.ID, ParentArticleID est une clé étrangère à la même table, ProductSupportArticles.ProductSupportArticleID. J'ai une contrainte de vérification ProductSupportArticleID != ParentArticleID de sorte qu'un article ne peut pas être son propre parent.

Cependant, un article de l'assistance se rapportant à un produit en particulier ne devraient pas être en mesure d'être le parent ou l'enfant d'un article relatif à un produit différent. Comment puis-je ajouter une contrainte de vérification ou similaire en disant: (ProductID = (SELECT ProductID FROM ProductSupportArticles P WHERE ParentArticleID = P.ProductSupportArticleID))

Ou comment dois-je mettre en œuvre mes tables différemment?

Utiliser un déclencheur ou un UDF. Vérifier les contraintes ne peuvent pas quitter l'enregistrement en cours, mais par l'UDF, vous pouvez accéder à d'autres dossiers/tables.

OriginalL'auteur Jake Petroules | 2011-03-20

  1. 5
    1. Créer une contrainte UNIQUE sur (ProductSupportArticleID, ProductID).
    2. Ont un FK reportez-vous (ParentArticleID, ProductID) à (ProductSupportArticleID, ProductID)

    Avertissement: l'application de règles d'entreprise via Udf enveloppé dans les contraintes de VÉRIFICATION a plusieurs lacunes. Par exemple, ils peuvent donner des faux positifs et des faux négatifs pour le multi-ligne des modifications. Ils sont aussi très lent.

    aka cyberkiwi - avez-vous essayé dans la pratique? J'ai des dizaines de solutions de travail en production. Si ParentID est null, il ne se réfère pas à un parent en ligne.
    aka cyberkiwi: l'auto-référencement FKs fonctionne bien. Pour un exemple de travail, de google "Contigus à des Périodes de Temps" et "la Dénormalisation à imposer des règles d'entreprise: les Totaux cumulés". Si cela ne fonctionne pas pour vous, les erreurs que vous obtenez - nous pouvons vous aider.
    +1, Vous avez raison. Je n'avais pas conscience d'un demi-FK n-uplet (null,valeur) ne déclenche pas un FK vérifier. Cela fonctionne très bien
    Désolé, je suis a la suite de ces quatre mois plus tard (suis dérouté), mais la seule raison pour laquelle la contrainte UNIQUE est nécessaire que la clé étrangère de travailler (depuis les colonnes doit correspondre à un existant PK/contrainte UNIQUE, correct? Sinon, il aurait sans effet depuis ProductSupportArticleID est primaire?
    Petroleus: Oui, un FK doit se référer à un PK/contrainte UNIQUE ou un index UNIQUE. C'est la seule raison pour laquelle nous avons besoin de cette contrainte UNIQUE pour.

    OriginalL'auteur A-K

  2. 2

    Échantillon de travail

    Exemples de tables:

    create table products (productid int primary key)
insert products select 1
insert products select 2
GO

create table ProductSupportArticles (
ProductSupportArticleID int NOT NULL primary key,
ParentArticleID int NULL references ProductSupportArticles(ProductSupportArticleID),
ProductID int NOT NULL references products (productid),
Title varchar(100) NOT NULL,
Content varchar(MAX) NOT NULL
)
GO

    Fonction de Support

    create function dbo.getProductSupportArticleParent(@ParentArticleID int)
returns int
with returns null on null input
as
begin
return (select ProductID from ProductSupportArticles where ProductSupportArticleID = @ParentArticleID)
end
GO

    La contrainte

    alter table ProductSupportArticles add check(
    ParentArticleID is null or
    dbo.getProductSupportArticleParent(ParentArticleID) = ProductID)
GO

    Tests

    insert ProductSupportArticles select 1,null,1,3,4
insert ProductSupportArticles select 2,null,1,3,4
insert ProductSupportArticles select 3,null,2,3,4
insert ProductSupportArticles select 4,1,1,3,4

    Ok jusqu'à présent, celui-brise, car 5 est apparenté par 1, qui appartient au produit 1.

    insert ProductSupportArticles select 5,1,2,3,4

    MODIFIER

    Alex a souligné valide faille. Pour couvrir ce scénario, vous auriez besoin d'un déclencheur de mise à JOUR qui va propager les modifications apportées à un enregistrement de ProductID à tous les enfants (et descendant) de dossiers. Ce serait un simple déclencheur, donc je ne vais pas mettre le code ici.

    Cette solution a un vide que rien dans votre solution, qui nous empêche de changer de ProductID de la ligne parent, et le laisser inchangé pour l'enfant.

    OriginalL'auteur RichardTheKiwi