SQL Server 2005: Nullable Foreign Key Constraint

Question

I have a foreign key constraint between tables Sessions and Users. Specifically, Sessions.UID = Users.ID. Sometimes, I want Sessions.UID to be null. Can this be allowed? Any time I try to do this, I get an FK Constraint Violation.

Specifically, I'm inserting a row into Sessions via LINQ. I set the Session.User = null; and I get this error:

An attempt was made to remove a relationship between a User and a Session. However, one of the relationship's foreign keys (Session.UID) cannot be set to null.

However, when I remove the line that nulls the User property, I get this error on my SubmitChanges line:

Value cannot be null.
Parameter name: cons

None of my tables have a field called 'cons', nor is it in my 5,500-line DataContext.designer.cs file, nor is it in the QuickWatch for any of the related objects, so I have no idea what 'cons' is.

In the Database, Session.UID is a nullable int field and User.ID is a non-nullable int. I want to record sessions that may or may not have a UID, and I'd rather do it without disabling constraint on that FK relationship. Is there a way to do this?

Answer 1

From the DB-view: no, there is no way. Either you have a FK-constraint on a field, thus binding it to a PK-field of another table or you do not have the FK.

You can't agree on the contract "never let this UID be something that is not included in the list of PK" and then deliberately break it. (And NULL is not a unique PK.)

I'd suggest you rig up a trigger for the UID-table, that checks against the PK/ID-table. Some kind of "my own FK, but not quite"-constraint :)

Answer 2

I seemed to remember creating a nullable FK before, so I whipped up a quick test. As you can see below, it is definitely doable (tested on MSSQL 2005).

Script the relevant parts of your tables and constraints and post them so we can troubleshoot further.

   
CREATE DATABASE [NullableFKTest]
GO
USE [NullableFKTest]
GO
CREATE TABLE OneTable 
(
    OneId  [int] NOT NULL,
    CONSTRAINT [PK_OneTable] PRIMARY KEY CLUSTERED 
    (
     [OneId] ASC
    )
)
CREATE TABLE ManyTable (ManyId  [int] IDENTITY(1,1) NOT NULL, OneId [int] NULL)
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ManyTable_OneTable]') AND parent_object_id = OBJECT_ID(N'[dbo].[ManyTable]') )
ALTER TABLE [dbo].[ManyTable]  WITH CHECK ADD CONSTRAINT [FK_ManyTable_OneTable] FOREIGN KEY([OneId])
    REFERENCES [dbo].[OneTable] ([OneId]) 
GO

--let's get a value in here
insert into OneTable(OneId) values(1)
select* from OneTable

--let's try creating a valid relationship to the FK table OneTable
insert into ManyTable(OneId) values (1) --fine
--now, let's try NULL
insert into ManyTable(OneId) values (NULL) --also fine
--how about a non-existent OneTable entry?
insert into ManyTable(OneId) values (5) --BOOM! - FK violation

select* from ManyTable
--1, 1
--2, NULL

--cleanup
ALTER TABLE ManyTable DROP CONSTRAINT FK_ManyTable_OneTable
GO
drop TABLE OneTable
GO
drop TABLE ManyTable
GO
USE [Master]
GO
DROP DATABASE NullableFKTest