• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

nullabe foreig keys

In my application employee table has many fields are optional but when present has to be from some other tables, example department-id, the employee can be created with out a department, but when assigning a department it should be a department from DEPARTMENT table. Shall I force the foreign key? though it's nullable. any hidden issues when going for a nullable foreign key? I am using Oracle 10g.
0
gksuresh_ee
Asked:
gksuresh_ee
  • 4
  • 4
  • 3
5 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
<< Shall I force the foreign key? though it's nullable >>

Yes. You can create a Foreign key on a table that allows NULL values. No hidden issues with it.
That's the reason why Oracle provide you with the option to create Foreign Key on both Nullable and Not Nullable columns.

But make sure with the usage of Index on that column. If you have many Nullable columns avoid creating Index on that Foreign Key.
0
 
dportasCommented:
I find a few potential problems with nullable foreign keys. The way compound nullable foreign keys work is not appropriate for most cases. Also some people will tend to write joins against the foreign key columns not expecting them to be nullable and get incorrect answers as a result.

In my opinion nullable foreign keys is a complexity best avoided. So I'd suggest decomposing your foreign key into a new table with non-nullable columns (ie, vertical decomposition). That way everything works as expected and you don't have any nulls to deal with.
0
 
gksuresh_eeAuthor Commented:
Do you think nullable foreign keys can be avaided always, I have cases where it looks impossible. In such cases are you suggesting not to force the constraintin databse instead make sure code takes care of it?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
dportas,
     There can be situations where Foreign Keys needs to be Nullable.
And that depends upon the Business requirements. And I hope there should not be any issues around having a Null value in a foreign key column.
0
 
dportasCommented:
Logically speaking it is always possible to model anything without nulls. A technique I like to use is to prototype any data model entirely without nulls and then add nullable columns only where I find some special advantage to doing so. The main reason to use a null is if it allows you to implement some logic that the DBMS otherwise cannot handle efficiently.

Generally speaking I can see disadvantages to making a foreign key nullable but not many advantages.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
dportas,
      I will give you a basic example where Foreign Key is a Not Null Column.
Lets Say you have Employee table which has the following columns.

EmplD, EmpName, DOB, Gender, Sal, DateofJoining, ManagerID, etc..

ManagerID is a Foreign Key which Self References Employee table on EmpID.

For 1 - 5 Employees in any Organization, they wont have Managers and ManagerID will be Null.
0
 
dportasCommented:
>> I will give you a basic example

No nulls if you do it this way:
 
CREATE TABLE Employee
(EmplD INTEGER NOT NULL  PRIMARY KEY,
EmpName VARCHAR... );

CREATE TABLE EmployeeManager
(EmplD INTEGER NOT NULL  PRIMARY KEY REFERENCES Employee (EmpID),
ManagerID INTEGER NOT NULL REFERENCES Employee (EmpID));

Nulls are only an abstraction used by database designers. They have nothing to do with business requirements because all facts in a database can be represented either with or without nullable attributes. When evaluating whether to use a null or not I think it's important to distinguish between database implementation issues and the real world problem that you are trying to solve. The database implementation may or may not have nulls but the real world never does.

0
 
gksuresh_eeAuthor Commented:
Thank you very much for a good example and the concepts.
One last question before accepting it as a solution,
If I apply the principle of avoiding nullable columns, won't it increase the number of tables hence JOINS required in quiries resulting in a slower performance? where ever performance is a concern, can I stick to the de-normalized form of nullable FKs?
Once again thanks to both of you for a good debate, and valuable information.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
dportas,
    I understand your point of avoiding nulls but that makes the table more normalized and the general suggestion for any real time applications is to have Normalizations upto 3NF or less than 3NF to obtain better performance.
Otherwise it may lead to performance degradation of the application.

gksuresh_ee,

<< where ever performance is a concern, can I stick to the de-normalized form of nullable FKs? >>

Yes. It is true, For our real time applications it better to stay with 3NF as mentioned above. And in your case, I think there is no issues with having a Nullable Foreign Key for the reason mentioned above
0
 
dportasCommented:
It's unwise to generalise about performance without looking at specific queries, indexing, storage and so on. When you decompose a table it's a fair assumption that some operations on the new tables will be faster (because the total size of data is smaller) while some others may be slower. As always, it depends.

Also, as I already explained, a null is never part of the business requirement. Wherever a database designer puts in a null it's usually the case that an application programmer has to create some code to remove or work around the null (in display, computations or reports for example). From the application point of view therefore nulls will very likely add at least some development cost and processing overhead.

Normalization has nothing to do with performance because in all cases performance is determined by the physical implementation and the physical operations applied to the data rather than by the logical design.  In fact one of the benefits of normalization is that it tries to reduce query "bias" - ie find a balanced design that can best serve all logical operations on the data.

As a matter of detail, the normal forms, including Third Normal Form (3NF) are all based on relations *without* nulls. To say that a table with nulls is in 3NF is a necessarily arbitrary and subjective interpretation of what normalization really means. My suggestion is that you aim for any database design to be in at least BCNF (and generally 5NF) unless and until you find a reason to change that. Do not begin your design process with a design based on nulls and denormalization.

Hope that helps.
0
 
gksuresh_eeAuthor Commented:
Thank you both for ypur time and quick updates. I still have some confusions, bit the comments were really useful and knowledgable
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now