Q

Foreign keys

Foreign keys are used to restrict irrelevant data entry into the table. If it's handled in the front end, is it

necessary to create a foreign key at the back end (database)? Also, if I have too many foreign keys in a table, will it affect the performance? Because for every DML statement, N number of master tables have to be checked.

Your choice of adjective "irrelevant" is amusing. "Bad" or "wrong" might be better.

The purpose of foreign keys is to ensure one aspect of data integrity. There are actually several types of data integrity:

  • entity integrity, which ensures that every entity can be uniquely identified (implemented via primary keys)

  • domain integrity, which ensures that attribute values are chosen only from a specific set of allowed values

  • referential or relational integrity, which ensures that every foreign key is either NULL (if allowed) or contains a value that matches some value of the related primary key

If you attempt to enforce relational integrity without an actual foreign key defined in the database, then you run the risk of having a "broken link" or "orphan record" in the database. In other words, data integrity will be violated, your database will have bad data, and your users will get angry (or worse).

You will never be certain that your front end program will be the only means for changes to enter the database. It's just too big a risk. Furthermore, you are spending unnecessary and likely substantial time and effort to create logic which the database has built in. You are re-inventing the wheel for no good reason.

There can never be "too many" foreign keys. They are either required by the business rules, or not. If they are required (for example, to ensure that every order belongs to a known customer), then you must implement them. Performance is not the issue. Without foreign key checking, your queries might run a few picoseconds faster, but if the database then contains bad data, how long will your users tolerate such a situation?

Trust me, it is far, far better to use foreign keys for their intended purpose than to try to do it yourself or try to get away without them.


This was first published in September 2004

Dig deeper on Oracle DBA jobs, training and certification

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close