Adding a null foreign key.

Question

I have two tables constructed in this way (this is just a simplified and generic example):

Person Table ----------- p_Id, f_name, l_name Job Table ---------- job_Id, job_desc 

I want to add a foreign key column, Person.job_Id, which can be null, referring to Job.job_Id (PK). The reason is that the task may not be known in advance, so it may be zero. Having a “Other” is not an option.

I have had it so far, but I get "can not create constraint."

 ALTER TABLE dbo.Person ADD job_Id INT FOREIGN KEY (job_Id) REFERENCES dbo.Job(job_Id) 

Thanks in advance.

Answer 1

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

Answer 2

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