Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 6 down vote accepted

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)

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.

share|improve this answer
Thanks for the detailed explanation. – dangowans Sep 27 '12 at 14:48
+1 This seems to be the simplest solution. – A-K Sep 27 '12 at 21:21

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.