Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sorry, if this is naive question, but I searched a lot and did not get a vivid answers. Consider a simple InnoDB FOREIGN KEY without ON DELETE or ON UPDATE

  1. I think this will avoid INSERT of a value which does not exist in the parent table. Is there any other advantage?

  2. Does this FOREIGN KEY assist a better JOIN on the corresponding key?

  3. Without FOREIGN KEY a query merely deals with an independent table. Will FOREIGN KEY slow down the mysql performance (even trivial) due to the link with another table? e.g. in SELECT and INSERT.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

Having built databases since before there were efficient database engines, I would recommend you create FOREIGN KEY constraints where required.

  1. Presence or absence of ON DELETE and ON UPDATE does not avoid the INSERT requirement on the parent table. They will influence what happens when the parent key is changed or removed. The parent record will be required if the child table's key is not nullable.
  2. The foreign key will enforce referential integrity and this will ensure that joins will work. Without the foreign key it will be possible to enter values in the child table which do not exist in the parent table. Outer joins to the parent table will fail for those records.
  3. Query speed will not slow down due to the FOREIGN KEY. There will be a minor impact on INSERT as the parent table's index will need to be check for presence of the parent record. This is not a reason to sacrifice referential integrity. (The cost to continually verify it by other means will usually outweigh the costs of the constraint.) The cost of cleaning up the referential integrity after the fact will greatly exceed the savings.

I avoid the ON DELETE and ON UPDATE actions in FOREIGN KEY definitions. I haven't yet designed a database where they are necessary. These two actions control what happens to the child records when the parent changes. ON DELETE can be extremely destructive if a frequently referenced parent is unintentionally deleted. ON UPDATE is only useful where the parent key can change. Use of surrogate keys eliminates the need for ON UPDATE actions.

If the reverence to the parent table is always from the child it might be appropriate not to index the foreign key column in the child table.

share|improve this answer
    
Very helpful answer. But I believe ON DELETE is quite useful. For example, when deleting a post, comments are not necessary and should be deleted to. Here in stackexchange, when deleting a Questions, Answers should be deleted by ON DELETE. –  All Feb 29 '12 at 4:05
1  
There are cases where ON DELETE is useful. However, I tend to work with systems with financial data or other requirements for data retention. In those cases ON DELETE has highly undesirable side effects. –  BillThor Feb 29 '12 at 4:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.