CODEWITHSUNDEEP
mysqlsqlforeign-keys
o0'.
o0'.

Reputation: 11863

Foreign keys when cascades aren't needed

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 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?

Upvotes: 0

Views: 115

Answers (2)

gaRex
gaRex

Reputation: 4215

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.

Upvotes: 2

Kurrija
Kurrija

Reputation: 130

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

Upvotes: 3

Related Questions