SQL Server 2005, Foreign key check against part of a table

Question

I have an articles table that references a categories table. I'm defining the foreign key like this:

constraint fk_categoryid foreign key (categoryid) references categories (categoryid)
on update no action
on delete no action

Is it possible in the foreign key definition to restrict the categories that can be referenced based on another column in the categories table? For example, say there is a hasarticles column in the categories table. I'd like the foreign key constraint to be restricted to those categories where hasarticles = true.

Answer 1

No this isn't possible.

I'd probably create a separate table categoriesWithArticles that holds the relevant categoryids (instead of having the flag) and have the FK reference that table instead.

Another more convoluted way to enforce it and keep the column would be

CREATE TABLE categories
(
categoryId int primary key,
hasarticles bit not null,
unique(categoryId, hasarticles)
)

CREATE TABLE articles
(
categoryId int,
hasarticles as cast (1 as bit) persisted,
FOREIGN KEY (categoryId, hasarticles) REFERENCES categories(categoryId, hasarticles)
)

But this requires the creation of a technically redundant unique constraint as well as the additional persisted column in the articles table.