SQL 添加可为空的外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9199826/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Adding a nullable foreign key
提问by Yatrix
I have two tables built like this (this is just a simplified and non-proprietary example):
我有两个这样构建的表（这只是一个简化的非专有示例）：
Person Table
-----------
p_Id, f_name, l_name
Job Table
----------
job_Id, job_desc
I want to add a foreign key column, Persons.job_Id, that can be nullable that references Job.job_Id (the PK) The reason is, the job may not be known in advance, so it could be null. Having an "Other" is not an option.
我想添加一个外键列，Persons.job_Id，它可以为空，引用 Job.job_Id（PK） 原因是，该作业可能无法提前知道，因此它可能为空。拥有“其他”不是一种选择。
I had this so far but I'm getting "could not create constraint".
到目前为止我有这个，但我得到“无法创建约束”。
ALTER TABLE dbo.Person
ADD job_Id INT FOREIGN KEY (job_Id) REFERENCES dbo.Job(job_Id)
回答by Mithrandir
Try it in two steps:
分两步试试：
ALTER TABLE dbo.Person ADD job_Id INT NULL;
ALTER TABLE dbo.Person ADD CONSTRAINT FL_JOB
FOREIGN KEY (job_Id) REFERENCES dbo.Job(job_Id);
回答by danicode
Try it like this, WITH NOCHECK:
像这样尝试，没有检查：
ALTER TABLE dbo.Person ADD job_Id INT NULL;
ALTER TABLE dbo.Person WITH NOCHECK ADD CONSTRAINT FL_JOB
FOREIGN KEY (job_Id) REFERENCES dbo.Job(job_Id);
回答by VO142857
Below is my solution with creating foreign key programmatically.
TestTable1 has substitute of FK that is either NULL or matches record in TestTable2.
TestTable2 has standard FK in TestTable1.
以下是我以编程方式创建外键的解决方案。
TestTable1 具有 FK 的替代品，该 FK 要么为 NULL，要么与 TestTable2 中的记录匹配。
TestTable2 在 TestTable1 中具有标准 FK。
CREATE Table TestTable1 (ID1 int IDENTITY UNIQUE, ID2 int NULL);
GO
CREATE Table TestTable2 (ID2 int IDENTITY UNIQUE, ID1 int NOT NULL foreign key references TestTable1(ID1));
GO
CREATE procedure CreateTestRecord1 @ID2 int null AS
begin
if @iD2 IS NOT NULL AND NOT EXISTS(SELECT * from TestTable2 where ID2 = @ID2)
begin
RAISERROR('Cannot insert TestTable1 record. TestTable2 record with ID %d doesnt exist', 16, 1, @ID2);
return;
end
Insert into TestTable1(ID2) OUTPUT Inserted.ID1 Values(@ID2);
end
GO
CREATE procedure LinkTable1toTable2 @ID1 int, @ID2 int NULL as
begin
if @iD2 IS NOT NULL AND NOT EXISTS(SELECT * from TestTable2 where ID2 = @ID2)
begin
RAISERROR('Cannot update ID2 in TestTable1 record. TestTable2 record with ID %d doesnt exist', 16, 1, @ID2);
return;
end
update TestTable1 Set ID2=@ID2 where ID1=@ID1;
select @@ROWCOUNT;
endGO
