CODEWITHSUNDEEP
sqlsql-server-2008foreign-keys
Yatrix
Yatrix

Reputation: 13785

Adding a nullable foreign key

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)

Upvotes: 17

Views: 53857

Answers (4)

Deepanshu Mehta
Deepanshu Mehta

Reputation: 1780

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;

Upvotes: 0

VO142857
VO142857

Reputation: 1

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

Upvotes: -3

danicode
danicode

Reputation: 887

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

Upvotes: 14

Mithrandir
Mithrandir

Reputation: 25377

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

Upvotes: 28

Related Questions