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?

score 27 · Accepted Answer · edited Jan 14 '14 at 01:22

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

Right adk. +1 The definition of a foreign key is that it's a value in one table that must match a primary key value in another table or be null. — Alan, Feb 22 '09 at 02:31
Logically, it makes sense, right? I read it like this: IF I try to reference a record in another table, make sure the reference is valid. If I don't (NULL), who cares. — andrej_k, Feb 24 '09 at 17:55

SQL Server 2005: Nullable Foreign Key Constraint

1 Answers1

Linked