CODEWITHSUNDEEP
sqlforeign-keysconventions
182764125216
182764125216

Reputation: 956

Advantage of nullable Foreign Keys

I need to build a table for some new components that will keep track of available and used inventory. It will be something like this:

CREATE TABLE components
(
   component_Id int NOT NULL PRIMARY KEY,
   workOrder_Id int FOREIGN KEY,
   componentSerial varchar(25) NOT NULL,
   foo varchar(50),
   bar int,
   information nvarchar(250)
   date DateTime
)

What advantages are there for having the FOREIGN KEY workOrderID be nullable when it hasn't been used yet as opposed to being NOT NULL and having a default value of 0?
To me letting it be nullable makes more sense.

I looked at some similar situations but I'm not sure why someone would choose one over the other.

Upvotes: 3

Views: 851

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562260

The advantage of a nullable foreign key is the same as the advantage of any other nullable column: if you have no known or applicable value to store in a given row, you can use NULL instead of electing a "special value" that signifies no value.

Upvotes: 6

bobince
bobince

Reputation: 536339

If you have a foreign key with a default value of 0, that means you must at all times maintain the existence of a workOrder with a ID of 0, as a fake-workOrder for unassigned components to reference. This is a pretty ugly hack.

(This is assuming your FOREIGN KEY actually is a proper, enforced foreign key, which is definitely desirable, but wouldn't happen if you were, say, using MyISAM. The example doesn't work for me, as just saying FOREIGN KEY on its own without specifying what column it REFERENCES isn't valid SQL.)

If you don't like NULLs, the alternative solution is a join table mapping components to workOrders, with a UNIQUE constraint on the component_Id.

Upvotes: 9

Related Questions