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)

Through

Do the data types of the 2 columns match? – Hinder

They need to.Job_Id is an Identity column of sequential ints. – Through

Anyone care to say why they downvoted so I can either change the question to make it better and/or learn from my mistake? – Through

Answer 1

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

Julienne

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. – Usanis

@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. – Julienne

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. – Through

Answer 2

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

Vietnamese

WITH NOCHECK helps when Foreign Key could be NULL – Dorsoventral

Looking at this answer, it seems that there could be performance side effects. Is this mandatory? – Nerve

Is this a good idea? I read that it allows values that are not in the referenced table. – Zigmund

I think it allows only NULL, not other values, that are not in the references table.. – Vietnamese

Answer 3

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;

Tman

Answer 4

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

Orontes