How to reference other tables in check constraints?

Question

I have a 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 is a foreign key to Products.ID, ParentArticleID is a foreign key to the same table, ProductSupportArticles.ProductSupportArticleID. I have a check constraint ProductSupportArticleID != ParentArticleID so that an article cannot be its own parent.

However, a support article pertaining to a particular product should not be able to be the parent or child of an article pertaining to a different product. How can I add a check constraint or similar saying: (ProductID = (SELECT ProductID FROM ProductSupportArticles P WHERE ParentArticleID = P.ProductSupportArticleID))

Or how should I implement my tables differently?

Answer 1

1. Create a UNIQUE constraint on (ProductSupportArticleID, ProductID).
2. Have a FK refer (ParentArticleID, ProductID) to (ProductSupportArticleID, ProductID)

Warning: enforcing business rules via UDFs wrapped in CHECK constraints has multiple loopholes. For example, they may give false positives and false negatives for multi-row modifications. Also they are very slow.

Answer 2

Working sample

Sample 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

Support function

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

The constraint

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 so far, this next one breaks it because 5 is parented by 1, which belongs to product 1.

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

---

EDIT

Alex has pointed out a valid flaw. To cover that scenario, you would need an UPDATE trigger that will propagate changes to a record's ProductID to all child (and descendant) records. This would be a simple trigger, so I won't provide the code here.