I am trying to visualize why/when you would apply an "optional" foreign key constraint -- where the foreign key
column can be NULL if it does not match the primary or unique key of the parent table.
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."
Dig deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.