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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: