Adding a nullable foreign key

Question

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.

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)

They need to.Job_Id is an Identity column of sequential ints. — Yatrix, Feb 08 '12 at 19:15
Anyone care to say why they downvoted so I can either change the question to make it better and/or learn from my mistake? — Yatrix, Feb 08 '12 at 19:27

score 27 · Accepted Answer · answered Feb 08 '12 at 19:12

27

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);

answered Feb 08 '12 at 19:12

Mithrandir

24,869
6
50
66

8

`ALTER TABLE dbo.Person ADD job_Id INT NULL,CONSTRAINT FL_JOB FOREIGN KEY (job_Id) REFERENCES dbo.Job(job_Id);` works too to do it in one statement. – Martin Smith Feb 08 '12 at 19:18
1

@MartinSmith : right, i'm a bit old school in these things and really like to do them one at a time, but his a very good to do it too. – Mithrandir Feb 08 '12 at 19:21
So, I created the Job table originally and then had to recreate it. When I recreated it, I forgot to create the primary key...dammit. Thanks for the help, all. – Yatrix Feb 08 '12 at 19:48

score 14 · Answer 2 · answered Sep 09 '15 at 15:05

14

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);

answered Sep 09 '15 at 15:05

danicode

807
9
17

WITH NOCHECK helps when Foreign Key could be NULL – Guillaume Raymond Jan 28 '16 at 13:30
Looking at this [answer](http://stackoverflow.com/a/1338565/755977), it seems that there could be performance side effects. Is this mandatory? – PhilDulac Nov 16 '16 at 17:30
Is this a good idea? I read that it allows values that are not in the referenced table. – Jess Jun 04 '20 at 15:33
I think it allows only NULL, not other values, that are not in the references table.. – danicode Jun 08 '20 at 08:14

score 0 · Answer 3 · answered Jun 21 '23 at 10:55

0

This will surely work in latest version:

giving you the example for profile table and Foreign Key user_id

ALTER TABLE profile CHANGE user_id user_id INT NULL DEFAULT NULL;

answered Jun 21 '23 at 10:55

Deepanshu Mehta

1,119
12
9

VO142857 · Answer 4 · 2019-03-20T17:11:39.083

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.

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

Adding a nullable foreign key

4 Answers4