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?

Use a trigger or a UDF. Check constraints cannot leave the current record, but through a UDF you can reach other records/tables. — RichardTheKiwi, Mar 21 '11 at 00:01

score 5 · Accepted Answer · answered Mar 21 '11 at 01:32

5

Create a UNIQUE constraint on (ProductSupportArticleID, ProductID).
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.

answered Mar 21 '11 at 01:32

A-K

16,804
8
54
74

@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. – A-K Mar 21 '11 at 02:26
@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. – A-K Mar 21 '11 at 14:03
+1 You are right. I didn't realise half a FK tuple (null,value) doesn't trigger a FK check. This works great – RichardTheKiwi Mar 21 '11 at 19:17
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? – Jake Petroules Jul 10 '11 at 21:40
@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. – A-K Jul 11 '11 at 14:52
Perfect, I've implemented this solution and it is working great. Thank you for all your help! PS - It's spelled "Petroules" ;) – Jake Petroules Jul 12 '11 at 01:40
@Jake Petroules: sorry for misspelling your name. – A-K Jul 13 '11 at 21:20

RichardTheKiwi · Answer 2 · 2011-03-21T01:43:09.250

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.

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. — A-K, Mar 21 '11 at 01:33

How to reference other tables in check constraints?

2 Answers2

EDIT