SQL Server 2005:可空的外键约束 [英] SQL Server 2005: Nullable Foreign Key Constraint

问题描述

我在表Sessions和Users之间有一个外键约束.具体来说，Sessions.UID = Users.ID.有时，我希望Sessions.UID为null.可以允许吗?每当我尝试执行此操作时，都会收到违反FK约束的情况.

具体来说，我是通过LINQ将一行插入到Sessions中.我设置了Session.User = null;我得到这个错误:

尝试删除用户和会话之间的关系.但是，关系的外键之一(Session.UID)不能设置为null.

但是，当我删除使User属性为空的行时，我在SubmitChanges行上收到此错误:

值不能为空.
参数名称:cons 

我的表中都没有一个名为"cons"的字段，在我的5,500行DataContext.designer.cs文件中也没有，在QuickWatch中也没有任何相关对象，因此我不知道什么是缺点.

在数据库中，Session.UID是可为空的int字段，而User.ID是不可为空的int字段.我想记录可能有或没有UID的会话，我宁愿在不禁用对FK关系的约束的情况下进行记录.有办法吗?

解决方案

我似乎还记得之前创建了可为空的FK，所以我进行了一次快速测试.正如您在下面看到的，它绝对是可行的(在MSSQL 2005上进行了测试).

编写表和约束的相关部分的脚本并将其发布，以便我们进一步进行故障排除.

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

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?

解决方案

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

这篇关于SQL Server 2005:可空的外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！