In this specific case (**), it does indeed make no sense. I can not think of any scenario where you would want an orderdetail that is not associated with an order.

But more in general, there definitely are situations where I would use a nullable foreign key. For instance in an Employee table, I might have a column AssignedToDept. Not all employees are assigned to a department, so for some this column is NULL. But for the employees that do have a value in this column, I want to make sure that this is a valid department, so I would add a foreign key constraint between this column and the Departments table.

(**) Many bad table designs come from mindlessly adding a surrogate key to each table. A proper database design method would first create an entire data model without any surrogate key. Only when ready to actually implement this logical data model into a physical database would surrogate keys be added to tables where this is warranted.

If that had been done for the use case of this question, the logical primary key of the OrderDetail table would have been a composite key, consisting of the combination of the primary key of the Order table and one extra column (probably LineNumber or DetailNumber). As the primary key, this would have been not nullable. Then, if at implementation time a surrogate key would have been considered appropriate for this table, the corresponding changes would have been made, but that would not have affected the nullability of this column.

And for full disclosure - the above is not a criticism of the question; I am aware that the question is a very simplified scenario targeted at testing a specific behaviour of foreign key constraints. But I do see mistakes like this in actual tables, and they never would have been made if the designer had not jumped the gun and started with a surrogate key on each and every table.