SQL Server 2005：可空的外键约束

具体来说,我通过LINQ将一行插入Sessions.我设置了Session.User = null;我收到这个错误：

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.

但是,当我删除用户属性null的行时,我的SubmitChanges行会收到此错误：

Value cannot be null.
Parameter name: cons

我的表都没有一个名为“cons”的字段,也不是在我的5,500行DataContext.designer.cs文件中,也不是QuickWatch中的任何相关对象,所以我不知道“cons”是什么.

在数据库中,Session.UID是一个可空的int字段,User.ID是一个不可空的int.我想记录可能有也可能没有UID的会话,而我宁愿在没有禁用该FK关系的约束的情况下执行它.有没有办法做到这一点？

解决方法

脚本表和约束的相关部分并发布,以便我们进一步解决疑难.

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!