I have two tables, one with columns accountid, category, code, name, desc and the other with columns id, accountid, code, reason, desc, active.
Is there any way I can create a constraint such that code in the second table contains only values of code that are in first table with condition where category is somevalue?
One method is a little trick. First, create a unique index on (category, code) in the first table:
create unique index unq_table1_category_code on table1(category, code)
Then create a computed column in table2:
alter table table2 add category as ('somevalue') persisted;
Then create the foreign key relationship:
alter table table2 add constraint fk_somevalue_code
foreign key (category, code) references table1(category, code);
category + code isn't unique in the referenced table. Although, one can always throw the parent table' PK in, just to guarantee uniqueness (assuming it exists, of course).
ALTER TABLE table2 WITH CHECK
ADD CONSTRAINT CK_table2category CHECK (category = 'somevalue'),
CONSTRAINT FK_table2code FOREIGN KEY (code) REFERENCES table1 (code);
ALTER TABLE [dbo].[StatusReason] WITH NOCHECK ADD CONSTRAINT [CK_StatusReason_ValueItem] CHECK([Category]='Status'), CONSTRAINT [FK_StatusReason_ValueItem] FOREIGN KEY([StatusCode]) REFERENCES [dbo].[ValueItem] ([Code]) GO ALTER TABLE [dbo].[StatusReason] CHECK CONSTRAINT [CK_StatusReason_ValueItem], [FK_StatusReason_ValueItem] GO
sql-serverto the list of other blue-boxes at the bottom of your question. Like what I've just done for you ;)