Optional foreign key can be NULL

Let's imagine an Employees table for a company, with the columns EmpID and EmpName. EmpID is the primary key, used to identify each employee.

Then let's imagine that there are a number of hot projects currently underway at this company, so let's have a Projects table with columns ProjID and ProjName.

Employees
 EmpID EmpName
 11034 Jones  
 20415 Smith  
 31524 Adams 
 43633 Brown
 67287 Bates

Projects
 ProjID ProjName
  21    Fix the payroll system
  22    Add a spinning logo to the Web site
  23    Design a new inventory system 

Okay, now let's say that each project can have only one project leader. This project leader must be one of our employees. So this means we'll add ProjLdr to the Projects table as a foreign key, and use the project leader's EmpID value in the ProjLdr column to relate back to the EmpID primary key in the Employees table.

Projects
 ProjID ProjLdr  ProjName
  21     20415   Fix the payroll system
  22     null    Add a spinning logo to the Web site
  23     31524   Design a new inventory system 

As you can see, project 22 does not have a project leader. Maybe nobody wants it, or else it hasn't been assigned yet. Consequently we use a NULL to indicate "unknown" or "not applicable" as the foreign key. An optional foreign key must allow NULL. If the foreign key were not optional, if it were NOT NULL, we would have to put some value in it for every row. And that, in turn, would mean that we would not be able to enter a project into the Projects table unless it has a project leader.

Now imagine the following conversation between the Chief Executive Officer and the Chief Information Officer at the weekly management meeting:

CEO: "Did your IT guys assign that Web site logo project yet? I don't even see it on the list of pending projects."

CIO: "No sir, we know it's pending but we can't add it yet."

CEO: "What do you mean you can't add it? We just spent a hunnert thousand on a new database system for you guys. I want all pending projects to be listed immediately."

CIO: "I understand, sir, but we don't have a project leader identified for that project yet."

CEO: "No wonder! If the project isn't on the list, how can we assign it to anybody? Nobody can even see what it's called or who the hint hint sponsor is."

CIO: "Okay, sir, what if we temporarily add it with Joe Blow as the project leader, that will at least get the project onto the list."

CEO: "Who's Joe Blow? I pride myself on knowing at least the name of everybody in the company, and we don't have a Joe Blow."

CIO: "No, sir, we'd have to invent him so that he could be the project leader for your logo project."

CEO (wondering if he needs to get a new CIO): "And would this fictitious Joe Blow thus end up on the payroll system?"

CIO: "Yes, sir, but I'm sure we can do that, we'll just set him up with zero salary..."

CEO: Negatory. You know Vanessa in Finance? She'd have heart failure. Besides, that's just plain dumb. Who's the bright guy on your staff that said you couldn't add a project without a project leader?"

CIO: "The DBA, sir. I'll draw up his dismissal papers for you to sign this afternoon, sir."