0

If I don't need to use cascade/restrict and similar constraints in a field which would logically be a foreign key, do I have any reason to explicitly declare it as a foreign key, other than aesthetics?

Wouldn't it actually decrease performance, since it has to test for integrity?

edit: to clarify, I don't need it since:

  • I won't edit nor delete those values anyway, so I don't need to do cascade and similar checks
  • Before calling INSERT, I'll check anyway if the target key exists, so I don't need restrict checks either

I understand that this kind of constraint will ensure that that relation will be still valid if the database becomes somehow corrupted, and that is a good thing. However, I'm wondering if there is any other reason to use this function in my case. Am I missing something?

o0'.
  • 11,739
  • 19
  • 60
  • 87
  • 2
    To me, the performance decrease would be a small price to pay for referential integrity. – Dan Bracuk Mar 25 '13 at 13:52
  • @DanBracuk sure, I guess I wasn't clear, I'll edit. – o0'. Mar 25 '13 at 13:53
  • 1
    You can think about it as some kind of etiquette OR some kind of good form. Everyone loves code with comments (especially when code is complex) and not likes code without them. Think of them as some kind of additional documentation also. – gaRex Mar 25 '13 at 14:32
  • gaRex yes, this question was if they were something more than that, and both your and @Kurrija's answers were good : ) – o0'. Mar 25 '13 at 14:47

2 Answers2

3

The answers to this quesiton might actually also apply to your question.

If you have columns in tables which reference rows in other tables, you should always be using foreign keys, since even if you think that you 'do not need' the features offered by those checks, it will still help guarantee data integrity in case you forgot a check in your own code.

The performance impact of foreign key checks is neglegible in most cases (see above link), since relational databases use very optimised algorithms to perform them (after all, they are a key feature since they are what actually defines relations between entities).

Another major advantage of FKs is that they will also help others to understand the layout of your database.

Edit: Since the question linked above is referring to SQL-Server, here's one with replies of a very similar kind for MySQL: Does introducing foreign keys to MySQL reduce performance

Community
  • 1
  • 1
Kurrija
  • 130
  • 7
  • The question you reference applies to SQL Server. – Kermit Mar 25 '13 at 14:20
  • @PolishPrince Sorry, added a link for MySQL as well, but many of the reasons for using FKs given in reply to the SQL Server question apply for just about all relational DBMS – Kurrija Mar 25 '13 at 14:26
  • 1
    I get data from a lot of different vendors and clients and I have never yet seen one that has good data if they have not set the FKs up formally. Data integrity is the first priority of databases design. Without it you wil eventually have garbage and your data wil become unrealiable. – HLGEM Mar 25 '13 at 14:29
2

You must to do it. If it will touch performance in write -- it's a "pixel" problem.

Main performance problems are in read -- FKs could help query optimizer to select best plan and etc. Even if you DBMS(-s) (if you provide cross-DBMS solution) will gain from it now -- it can happen later.

So answer is -- yes, it's not only aestetics.

gaRex
  • 4,144
  • 25
  • 37