I just did a little test, after setting the FK (as per code below). Then I added a couple of records to the EQ_FILE with different value in SITE_NO and NULL in CATEGORY. And no problem; no violation of FK constraint. Does it mean the SQL Server automatically ignores NULL values when checking FK constraints?

>You cannot do conditional FK but it may work if column is NULL, not empty. If you want to get fancy, you have to write a trigger instead of FK.
>
>>Hi,
>>
>>How common is it to change the FK constraint that would ignore empty records. Here is an example of FK:
>>

>>ALTER TABLE [dbo].[eq_file]  WITH CHECK ADD CONSTRAINT [FK_eq_file_site_category] FOREIGN KEY([SITE_NO],[category])
>>	REFERENCES [dbo].[calpmcat] ([SITE_NO],[CATEGORY])
>>	ON UPDATE CASCADE
>>	ON DELETE SET DEFAULT
>>

>>
>>The problem with the above is that table EQ_FILE could have any number of records where the field CATEGORY is empty string. Which means that the CALPMCAT has to have a record with empty CATEGORY for every SITE_NO that exists. Which does not make sense to me.
>>And the only way I see to get around it is to change the above expression to ignore records with empty CATEGORY.
>>My questions:
>>1. How do you modify the expression to ignore empty field?
>>2. Is this a good/common practice?
>>
>>TIA