Nullable Foreign Key bad practice?_问答_开发者

Let's say you have a table Orders with a foreign key to a Customer Id. Now, suppose you want to add an Order without a Customer Id, (whether that should be possible is another question) you would have to make the foreign key NULL... Is that bad practice or would you rather work with a link table between Orders and Customers? Although the relationship is 1 to n, a link table would make it n to n. On the other hand, with a link table, I don't have those NULLS anymore...

There won't actua开发者_如何学Golly be a lot of NULL's in the database, because a record with a foreign key to NULL is just temporarily until a customer for the order is added.

(In my case it isn't an Order and a Customer).

EDIT: What about a unassigned Customer to link to?

No There is nothing wrong with Nullable FKs. This is common when the entity the FK points to is in a (zero or one) to (1 or many) relationship with the primary Key referenced table.

An example might be if you had both a Physical address and a Mailing address attribute (column) in a table, with FKs to an Address table. You might make the Physical address nullable to handle when the entity only has a post office box (mailing address), and the mailing address nullable to handle when the mailing address is the same as the physical address (or not).

Having the link table is probably a better option. At least it does not violate normalization BCNF (Boyce-Codd normal form). however I would favor being pragmatic. If you have very few of these null values and they are only temporary I think you should skip the link table since it only adds complexity to the scheme.

On a side note; using a link table doesn't necessarily make it n to n, if you in the link table use the foreign key that's pointing to your orders table as the primary key in that link table the relationship is still 1..n. There can only be one entry in that link table per order.

Nullable columns can be in 1NF through 5NF, but not in 6NF according to what I've read.

Only if you know better than Chris Date "what first normal form really means". If x and y are both nullable, and indeed in some row x and y are both null, then WHERE x=y does not yield true. This proves beyond reasonable doubt that null is not a value (because any real value is always equal to itself). And since the RM prescribes that "there must be a value in every cell of a table", any thing that possibly contains nulls, is not a relational thing, and thus the question of 1NF doesn't even arise.

I've heard it argued that Nullable columns in general break the first degree of normalization.

See above for the sound reason underlying that argument.

But in practice it's very practical.

Only if you're immune to the headaches that it usually causes in the entire rest of the world. One such headache (and it's only a minor one, comparatively to other null phenomenons) is the fact that WHERE x=y in SQL actually means WHERE x is not null and y is not null and x=y, but that most programmers simply aren't aware of that fact and just read over it. Sometimes without any harm, other times not.

In fact, nullable columns violate one of the most fundamental database design rules : don't combine distinct information elements in one column. Nulls do exactly that because they combine the boolean value "this field is/is not really present" with the actual value.

I can't see anything wrong with that it is just an optional n-1 relationship that will be represented with a null in the foreign-key. Otherwise if you put your link table then you'll have to manage that it doesn't become a n-n relationship, so causing even more trouble.

Optional relationships are definitely possible in the relational model.

You can use nulls to express the absence of a relationship. They are convenient, but they will cause you the same headaches that nulls cause you elsewhere. One place where they don't cause any trouble is joins. Rows that have a null in the foreign key don't match any rows in the referenced table. So they drop out of an inner join. If you do outer joins, you are going to be dealing with nulls anyway.

If you really want to avoid nulls (6th normal form), you can decompose the table. One of the two decomposed tables has two foreign key columns. One is the optional foreign key you have, and the other is a foreign key referencing the primary key of the original table. Now you have to use constraints to prevent the relationship from becoming many-to-many, it you want to prevent that.

Using NULL would be a good way to clean up incomplete orders:

SELECT * FROM `orders`
WHERE `started_time` < (UNIX_TIMESTAMP() + 900) AND `customer_id` IS NULL

The above would show orders older than 15 minutes without a related customer ID.

If you are only adding the order temporarily with no customer id until a customer is defined, would it not be simpler to add the customer and order in a single transaction, thereby removing the need for the NULL foreign key entry and avoiding any constraints or triggers you've set up being violated?

Normally this situation arises ins web apps where the order is detailed before the customer defines who he/she is. And in those situations the order is kept in server state or in a cookie until all the necessary state for a complete order is supplied at which point the order is persisted to the database.

NULL foreign keys are ok for things like addresses, as mentioned above. But a NULL customer field doesn't make sense for an order and should be constrained.

You could always add an artificial row to your Customer table, something like Id=-1 and CustomerName = 'Unknown' and then in cases when you would normally set your CustomerId in Order NULL set it to -1.

This allows you to have no nullable FKs but still represent the lack of data appropriately (and will save you from downstream users not knowing how to deal with NULLs).

Nullable FKs for optional many-to-one relations are totally fine.

I've heard it argued that Nullable columns in general are break the first degree of normalization. But in practice it's very practical.

Yes theres something wrong. Its not a foreign key if its nullable. Its database design by code. Maybe you make a zero link to unassigned. or "Unassigned" if your using a character col. Keep the integrity of your data 100%.