Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

1 Answer 1

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)
)

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.

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

Your Answer

 
discard

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.