Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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)
like image 360
Yatrix Avatar asked Feb 08 '12 19:02

Yatrix


People also ask

Can you make a foreign key nullable?

A table can have many foreign keys. A foreign key is nullable if any part is nullable. A foreign key value is null if any part is null.

Can I insert null values in foreign key?

Foreign keys allow key values that are all NULL , even if there are no matching PRIMARY or UNIQUE keys.

Can foreign key constraint have null?

Yes FK can be null. But if you dont want null value for foreign key then better you should opt for BEFORE INSERT Trigger on your child table. A much better way would be to just add a NOT NULL constraint on the FK-column in your child table (that is orders in your case).


2 Answers

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);
like image 81
Mithrandir Avatar answered Oct 03 '22 09:10

Mithrandir


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);
like image 37
danicode Avatar answered Oct 03 '22 09:10

danicode