20
Customers
 customer_id

Orders
 order_id
 customer_id fk

If I have two tables and define a foreign key on customer_id in the Orders table, by allowing it to be null I am saying that I can have an order that does not have a customer associated with it. As such, the notion of a nullable foreign key seems at odds with the purpose of a foreign key, which is to enforce this constraint.

Is there a simple example of a situation in which a nullable foreign key would be necessary? Or an argument in favor of permitting them?

eggdrop
  • 3,356
  • 5
  • 29
  • 32

8 Answers8

49

Imagine a table that holds the TODOs of a team. If a TODO is not yet assigned to a member of the team, its user_id is NULL. If it is not NULL it is a foreign key to the users table.

n3rd
  • 5,989
  • 4
  • 39
  • 56
  • 5
    The alternative, which many would prefer, is to have an "unassigned" user and assign unassigned TODOs to them. You can then make the column NOT NULL. Most books on database design discuss this issue at great length. –  May 29 '09 at 10:11
  • 2
    RE: Neil Butterworth I have worked with such a system, and I can say that NULL is much more obvious (What better describer for nothing than nothing) Now if performance polling shows that NULL foreign keys are a problem then avoid them, in fact that was why the system did, but otherwise you are doing a premature optimization. (IMHO) – Guvante Jul 11 '09 at 05:13
  • 14
    eggdrop asked if there was a use case in which a null FK was "necessary", not "possible". This answer is misleading because it suggests that such a case exists on an example which does not support the conclusion: adding a UserTasks (user_id, task_id) table with a user_id as its PK and task_id as a FK to the TODO table would support the exact same use case, only without using nulls. The question remains open which approach is better, but I don't believe a use case exists where it is impossible to avoid null FKs. – Tomislav Nakic-Alfirevic May 18 '11 at 14:23
  • 1
    +1 @Tomislav: This answer is not an example of a necessary nullable-FK. Such a thing doesn't exist. [molf's answer](http://stackoverflow.com/questions/925203/any-example-of-a-necessary-nullable-foreign-key/925257#925257) is the correct one. – Marcelo Cantos Aug 18 '11 at 06:11
  • I never claimed that nullable FKs are necessary, I was merely trying to provide an example where it would - in my opinion - make sense. I personally find "dummy records" to be a makeshift solution in most cases where a NULL value would be perfectly sensible. But that is probably more a matter of taste than anything. – n3rd Aug 25 '11 at 10:46
  • One could also have an `UnassignedTask` table and an `AssignedTask` table, the latter having an FK constraint to `User` and the former not. – ErikE Dec 27 '12 at 06:21
  • 1
    @TomislavNakic-Alfirevic: This question just came up on my radar, and I just realised that your schema has some errors in it. `user_id` should not be the primary key. That would allow each user only one task. `task_id` should be the primary key (meaning each task can belong to at most one user), and both columns should have foreign keys to their respective columns in the User and Task tables. – Marcelo Cantos Jun 18 '13 at 00:29
  • @n3rd: What's a "dummy record"? – Marcelo Cantos Jun 18 '13 at 00:30
  • @MarceloCantos Indeed, there is an error in the schema change I proposed. Thanks for pointing it out. – Tomislav Nakic-Alfirevic Jun 18 '13 at 08:50
  • @MarceloCantos: I meant the "unassigned user" (which is no "real" user) mentioned by anon in the first comment. – n3rd Jun 19 '13 at 10:59
18

No, nullable foreign keys are never necessary.

You can always normalize an optional 1-many relationship. Taking your example, you may have the following tables:

Customers: customer_id, ...
Orders: order_id, ...
OrdersCustomers: order_id, customer_id
  UNIQUE(order_id)

The two unique constraints make sure that one order can belong to only one customer, and never to the same customer twice.

Whether you should always normalize such a relationship is a different story. In some cases denormalization may lead to simpler implementations.

Nikunj Madhogaria
  • 2,139
  • 2
  • 23
  • 40
molf
  • 73,644
  • 13
  • 135
  • 118
  • 2
    +1, but I disagree with the final paragraph. Denormalization only superficially simplifies things. It will always bite you in the long run. The only valid reason to do so is to accommodate the current SQL engines that punish you by performing poorly in the presence of correct normalization. (A properly designed relational engine would allow you to define the above normalized schema as well as an automated and completely transparent mapping to an efficient denormalized storage layout, thus giving you the best of both worlds.) – Marcelo Cantos Aug 18 '11 at 06:05
  • @Marcelo - "Denormalization only superficially simplifies things" - Not true. As [n3rd](http://stackoverflow.com/a/925220/165673)'s example demonstrates, there are absolutely instances where enforcing normalization ends up obscuring what is intended to be an optional relationship- Something that Nullable FKs make immediately clear. If NULLs are disallowed when modeling optional relationships, then custom default values must be introduced, which introduces complexity- and potential confusion- for programmers and admins that is *much* more likely to "bite you in the long run". – Yarin Jun 16 '13 at 19:35
  • 1
    @Yarin: The scenario n3rd cites doesn't require NULLs or special values, nor would a properly normalized solution obscure the intent. On the contrary, the intent would be explicit and unambiguous: `Todo { todoId PK, … }`, `UserHasTodo { todoId PK FK Todo(todoId), userId FK User(userId) }`. – Marcelo Cantos Jun 17 '13 at 23:48
  • 1
    @Yarin: In fact, I just noticed that two years ago I up-voted Tomislav's comment on n3rd's answer. It addresses precisely this point and proposes the same form of solution that I just did (though he got the PKs and FKs wrong). – Marcelo Cantos Jun 18 '13 at 00:24
  • @Marcelo- I'd argue that introducing a join table for this case *would* both introduce complexity (extra table) *and* obscure intent (0/1-to-many constraint is no longer enforced by DB) – Yarin Jun 19 '13 at 12:59
  • 1
    @Yarin: Adding a table does not automatically qualify as an increase in complexity. If that were true, one table holding both users *and* tasks would be simpler than one table for each. Also, NULLs induce all manner of subtle bugs that are difficult to even diagnose, let alone fix; they add far more complexity than an extra table. As for the constraint-enforcement comment, I don't know which case you're referring to, the one in the original question or n3rd's TODOs example. But either way, molf's answer explicitly addresses how to enforce the necessary constraints. – Marcelo Cantos Jun 19 '13 at 14:20
  • @Marcelo- Sorry you're right, I overlooked molf's example- You can indeed enforce the constraint with the normalized solution. Still not convinced it's the *simpler* solution, but good debate nonetheless. – Yarin Jun 19 '13 at 16:50
  • 1
    The second key is redundant. (Since each order_id is unique, so must every order_id-customer_id pair be.) If you want at most one order_id per customer_id then make customer_id unique. – philipxy Aug 15 '15 at 09:23
  • @philipxy I was thinking along the same lines. The second UNIQUE constraint is redundant according to the purpose -- "The two unique constraints make sure that one order can belong to only one customer, and never to the same customer twice" quoted in the answer. The first UNIQUE constraint also ensures the same. – Nikunj Madhogaria Aug 15 '15 at 19:13
  • It's redundant period, mathematically. PS I realize that you didn't specify "at most one order_id per customer_id"; I mentioned it for completeness/symmetry/comparison re the two associated entity types. – philipxy Aug 16 '15 at 00:12
5

As such, the notion of a nullable foreign key seems at odds with the purpose of a foreign key, which is to enforce this constraint.

The purpose of a foreign key is the make explicit the concept the random integer in the Orders table actually refers to an item in the Customers table. Actually enforcing that as a constraint is incidental.

James Curran
  • 101,701
  • 37
  • 181
  • 258
3

To set a foreign key nullable or null use below sql script

ALTER TABLE Return_COMMENTS MODIFY order_ID Number NULL;
animuson
  • 53,861
  • 28
  • 137
  • 147
mehbooblal mujawar
  • 31
  • 1
1

there is, make some tree structure, a table which related to itself. Consider this:

table_node(node_id, parent_node_id, name)

For the root, parent_node_id should be null, right?

goFrendiAsgard
  • 4,016
  • 8
  • 38
  • 64
  • You might argue that using "is_root" field would be a better solution to avoid nullable foreign key need. But by enforce to use "is_root", "parent_node_id" will be meaningless. Should we forced to fill meaningless data only because it should not be null? – goFrendiAsgard Nov 11 '11 at 01:50
  • what about simply setting the parent_node of the root node = to itself. – Bill Anton Oct 09 '12 at 20:53
  • good idea, but it would make "inserting" command more complicated if you make node_id as auto-increment. – goFrendiAsgard Oct 11 '12 at 13:38
  • yes, good point. the application would still have to perform a check on retrieval either way...(WHERE node_id = parent_node_id) vs (WHERE parent_node_id IS NULL)...so i agree using NULL is probably best. – Bill Anton Oct 11 '12 at 14:57
  • @goFrendiAsgard It's not very complicated to insert a single root node. You only do it once, and you can assume it matches the identity property, so no big deal. – ErikE Dec 27 '12 at 06:23
  • @banton Checking for NULLness always bites you in the long run. – ErikE Dec 27 '12 at 06:24
  • @EriKE: Okay, let me revise my answer. In some case, we use such a structure to store "graph-like" structure (navigation menu for example). Therefore, sometimes there are more than one root node. – goFrendiAsgard Dec 27 '12 at 07:03
  • @goFrendiAsgard You never need more than one root node. If there is "more than one root node" then you make a true root node above that. – ErikE Dec 27 '12 at 09:20
  • @ErikE: What I mean is something like this: https://github.com/goFrendiAsgard/No-CMS/blob/master/install/resources/database.sql#L255 In this case, I don't think adding 1 row of "true root node" is better. – goFrendiAsgard Dec 28 '12 at 02:17
  • @ErikE: exactly as it shown. I think when you click the link, you'll be directed to line 255, aren't you? There is a cms_navigation table. Imagine it as "menu". There will be many "menu" with many "sub-menu". Every "menus" are root. So in this case we have more than one root-node. And in this case, making one "true-root" node is insufficient for me. What do you think? – goFrendiAsgard Dec 29 '12 at 00:09
  • @goFrendi My total apologies--I followed the link on my phone and missed the line# part. Using `NULL` is a totally fine way to do it and I have designed tables this way, myself (I didn't mean to make it sound like it really wasn't good, just that it's not *required*). Creating a true root node is also possible. Adding another one-to-zero-or-one relationship table to record the parent would also work. The ultimate point is that the `NULL`able foreign key is, as far as I can tell, never *required*. In your answer you say "it *should* be NULL" but I disagree with that. – ErikE Dec 29 '12 at 00:50
0

A common design scenario for setting a column to null is if you have a one to many parent child relationship, but the children do not have to be present.

When a parent record (like an account) has children that can be deleted (like help requests) this is a good usage.

You may have a column for MostRecentRequest that includes the ID of the most recent help request. When the request is removed from the system the MostRecentRequest column is set to NULL meaning there are none present.

Another example is when you want NULL to mean that a row has not been assigned a parent yet. Perhaps you have help requests that come in and a NULL on the technician field means that no tech is assigned to the request. When you delete that technician you want all his uncompleted work to go back to the pool by resetting the value to NULL.

Jason Short
  • 5,205
  • 1
  • 28
  • 45
  • If the FK is in the parent, then it is not really a parent-child relationship, because the use of "parent-child" implies that there can be many children. When the relationship is one-to-zero-or-one, it is more of an "is a" than a "has a". In this case, make the child ID be the same as the parent ID, put the FK in the child (with a PK on the same column) and the NULLable problem entirely goes away. So no NULLable column is required, and in fact is (in my opinion) a inferior to using the supertype/subtype design pattern. – ErikE Dec 27 '12 at 06:28
0

There is another situation that I can think of:

Within one table, you have an ID field that uniquely identifies that record. That same record may (or may not) have a single parent.

Table with the following columns:

  • id as an integer, auto-increment, not nullable
  • parentid as an integer, nullable.

With a foreign key constraint, you can ensure that if the field is not null, then it refers to a valid parent. Furthermore, if you attempt to delete the parent record without fixing the children, then the parent cannot be deleted.

user35239
  • 1
0

We have lots of these things as our application is something that starts with some basic information for an event and over time, as the event is more fully planned, more information is added. But when the information is added we want to make sure it meets the FK constraint. FKs are for data integrity but all data is not always known at the time the initial data is inserted, so nulls are allowed.

Nikunj Madhogaria
  • 2,139
  • 2
  • 23
  • 40
HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • It's still not **required** as one could have special "Unassigned" values to put in the FK column. – ErikE Dec 27 '12 at 06:29