We have several foreign keys that allows for NULLs. When we open up a table in SQL Studio Management and alter one of the columns with a value that is not allowable (not part of the primary key), it accepts the value, when it should not. It appears that when there are nullable columns in a foreign key that the foreign key doesn't work; it is bypassed. I am guessing that this is part of the functionality of SQL Server. Is there a way to get around this. I am told that there is no way to make all the columns for foreign keys in this database NOT NULL. Thus, is there a work around to get the foreign key to work when one or more of the columns is NULL?
As it states the good practice is to keep NULLs to a minimum in any database schema. However there's nothing fundamentally wrong with having a nullable Foreign Key constraint. So talking about relationship here such as referential integrity and nullability are inter-realted issues to each other. NOT NULL means that there must be a value relevant to that data type. And referential integrity means that if an attribute has a value, it must be defined in another table. http://www.akadia.com/services/dealing_with_null_values.html a good one to talk about using nULLS.
If you can't use an FK (which might also be because SQL Server is very particular about avoiding circular references, even if the columns in question are not the same) then you might look at creating triggers to mimic the FK behaviour. If it would be an FK with cascading deletes and/or updates, you also have to create a corresponding trigger on the reference table. You also have to create (a) corresponding trigger(s) on the reference table.
I am not reproducing that behavior. I have a FK on a table and a row where that value is null. When I try to update that value to a value that does not exist in the primary key table I get an error as I should. I do not think you should have to resort to triggers for this functionality and see nothing wrong with having nulls in a column used in a FK.
Do you have multiple columns in your FK with NULLS? I have a composite FK with 4 columns and for those 4 columns within the FK they can all be NULL.
Someone suggested doing something like this (I will work with a FK of 2 columns to make it easy): ALTER TABLE [dbo].[orders] WITH CHECK ADD CONSTRAINT [CK_orders] CHECK (([client_id] IS NOT NULL) OR ([client_id] IS NOT NULL AND [contract_id] IS NOT NULL) OR ([client_id] IS NULL AND [contract_id] IS NULL))) This is my FK. How would I incorporate the part (([client_id] IS NOT NULL) OR ([client_id] IS NOT NULL AND [contract_id] IS NOT NULL) OR ([client_id] IS NULL AND [contract_id] IS NULL))) into it. Can you have a FK and a constraint at the same time? ALTER TABLE [dbo].[orders] WITH NOCHECK ADD CONSTRAINT [FK_Orders_ClientContract_ClientId_ContractId] FOREIGN KEY([client_id], [contract_Id]) REFERENCES [dbo].[client_contract] ([client_id], [contract_Id]) GO ALTER TABLE [dbo].[orders] CHECK CONSTRAINT [FK_Orders_ClientContract_ClientId_ContractId]
No, but check out this explanation of your issue with Microsoft: INF:Foreign Key Constraint Behavior of Nullable Composite Keys http://support.microsoft.com/kb/153370 Looks like this is a "feature". If you can create, a surogate key on the primary key table and use that for your FK. That would result in faster joins anyhow. BTW, I see there's different and strong viewpoints out there about using nulls for FK values.
Just in case anyone ever comes across this issue... I came up with this code: ALTER TABLE orders WITH NOCHECK ADD CONSTRAINT CK_Orders_ClientId_ContractId CHECK ( ([client_id] IS NOT NULL AND [contract_Id] IS NOT NULL) OR ([client_id] IS NULL AND [contract_Id] IS NULL) ) It appears to accomplish what I need.
