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?

Astor

Use a trigger or a UDF. Check constraints cannot leave the current record, but through a UDF you can reach other records/tables. – Weatherley

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.

Mantle

@Richard aka cyberkiwi - have you tried in practice? I have dozens of such solutions working in production. If ParentID is null, it just does not refer to a parent row. – Mantle

@Richard aka cyberkiwi: self-referencing FKs work all right. For a working example, google up "Contiguous Time Periods" and "Denormalizing to enforce business rules: Running Totals". If it does not work for you, post the errors you are getting - we can help. – Mantle

+1 You are right. I didn't realise half a FK tuple (null,value) doesn't trigger a FK check. This works great – Weatherley

Sorry I'm following up on this four months later (got sidetracked), but the only reason the UNIQUE constraint is needed is for the foreign key to work (since the columns must match an existing PK/UNIQUE constraint, correct? Otherwise it would have zero effect since ProductSupportArticleID is primary? – Astor

@Jake Petroleus: Yes, an FK must refer to an existing PK/UNIQUE constraint, or a UNIQUE index. This is the only reason we need that UNIQUE constraint for. – Mantle

Perfect, I've implemented this solution and it is working great. Thank you for all your help! PS - It's spelled "Petroules" ;) – Astor

@Jake Petroules: sorry for misspelling your name. – Mantle

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.

Weatherley

This solution has a loophole - nothing in your solution prevents us from changing ProductID for the parent row, and leaving it unchanged for the child ones. – Mantle