Allowing null in a foreign key

Question

Allowing null in a foreign key

Doug Simmons

Ten Centuries

Points: 1373
More actions
November 23, 2006 at 8:39 pm

#170497

I have added these tables to my database (for example):
CREATE TABLE dbo.Departments (
DeptID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,
DeptName NVARCHAR(50) NOT NULL,
DeptDeleted BIT NOT NULL DEFAULT 0
);
CREATE TABLE dbo.Employees (
EmployeeID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,
// Other fields
DeptID INT NULL FOREIGN KEY REFERENCES dbo.Departments
);
If I then write:
INSERT INTO Employees (
// OtherFields,
DeptID = null
);
I get an error on the foreign key relationship.
Is there something else I need to do to allow the null to be inserted in the Employees table?
Doug
November 27, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 1

i'm pretty new at this myself but i don't think you can allow nulls in a FK column since it has to match up to the PK column. if you allowed nulls it would ruin the referential integrity

Doug Simmons Ten Centuries Points: 1373 More actions · Answer 2

Sometimes you need to allow null in order to simplify the design of the database or to prevent large amounts of wasted data. In this case employees either belong to a department at headquarters or to an outside agency. For headquarters staff, only the dept name is needed. For agency staff, I need a whole heap of data with other related tables as well.

Having two foreign keys with one of them always null solves loads of problems and makes accessing the data much easier as well.

When I got no reply, I finally thought I'd posted this on the wrong forum so I put it on the T-SQL forum and someone there solved my problem (I wasn't assigning the null correctly).

Apologies to all; I then forgot about this original post.

Many thanks to everyone for your help. It is much appreciated.

Doug

Lowell SSC Guru Points: 323518 More actions · Answer 3

maybe it's just a typo, or the FK automatically assumes the PK of the referenced table but I thought itshould it be this:

CREATE TABLE dbo.Departments (

DeptID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,

DeptName NVARCHAR(50) NOT NULL,

DeptDeleted BIT NOT NULL DEFAULT 0

);

CREATE TABLE dbo.Employees (

EmployeeID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,

// Other fields

DeptID INT NULL FOREIGN KEY REFERENCES dbo.Departments(DeptID)

);

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Doug Simmons Ten Centuries Points: 1373 More actions · Answer 4

If the foreign key name is the same as its relative column name in the other table and this is not a multi-column constraint, it is not actually necessary to enter the column name in the related table.

However, it certainly does no harm to do so and may well make the script more readable.

I'm just lazy I guess.

Doug

Ray M SSC-Insane Points: 21093 More actions · Answer 5

This is not valid sql sytax

INSERT INTO Employees (

-- // OtherFields,

DeptID = null

);

-- This however works

insert into Employees (DeptID)

values(NULL)

select * from Employees

Results

EmployeeID	DeptID
1	NULL

Doug Simmons Ten Centuries Points: 1373 More actions · Answer 6

You're right of course. The real code ignoring the other fields was this:

INSERT INTO Employees(DeptID)

VALUES(#Val(This.DeptID)#)

where #DeptID# is a ColdFusion variable which contained an empty string.

The real correct code is:

INSERT INTO Employees(DeptID)

VALUES(null#Val(This.DeptID)#)

Doug

Doug Simmons Ten Centuries Points: 1373 More actions · Answer 7

Sorry, the above still looks wrong because some of the code I typed has been deleted by the forum. The final line should be:

VALUES(open chevron cfif This.DeptID EQ "" close chevron null open chevron cfelse close chevron #Val(This.DeptID)# open chevron /cfif close chevron

Doug