SQL Server 2005：可空的外键约束

SQL Server 2005：可空的外键约束

我似乎记得以前曾创建过一个可为空的FK，所以我进行了一次快速测试。正如您在下面看到的，它绝对是可行的（在MSSQL 2005上测试）。

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

CREATE DATAbase [NullableFKTest]GOUSE [NullableFKTest]GOCREATE 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)GOIF 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 hereinsert into oneTable(OneId) values(1)select* from OneTable--let's try creating a valid relationship to the FK table oneTableinsert into ManyTable(OneId) values (1) --fine--now, let's try NULLinsert into ManyTable(OneId) values (NULL) --also fine--how about a non-existent oneTable entry?insert into ManyTable(OneId) values (5) --BOOM! - FK violationselect* from ManyTable--1, 1--2, NULL--cleanupALTER TABLE ManyTable DROP ConSTRAINT FK_ManyTable_oneTableGOdrop TABLE oneTableGOdrop TABLE ManyTableGOUSE [Master]GODROP DATAbase NullableFKTest