I have a question of interest:
I have 2 tables in mysql with InnoDb
.
table tbl_a
has a primary key, named a_id
;
table tbl_b
has a primary b_id
and a foreign key on tbl_a.a_id
with "ON DELETE NO ACTION
".
+-------------+---------------+---------------+ | Table Name | Primary Key | Foreign Key | +-------------+---------------+---------------+ | tbl_a | a_id | | | tbl_b | b_id | a_id | +-------------+---------------+---------------+
why should I still use InnoDb and foreign keys, if i don't really use the magic of foreign keys in the end in anyway?
Is there still a point of using
innodb and foreign keys
instead of
myisam and no foreign keys.
If I just do "NO ACTION
" on deletes or updates?
I hope you got my point of interest :)
Foreign keys cannot handle deletes and updates. Explanation: A foreign key is the one which declares that an index in one table is related to that in another and place constraints. It is useful for handling deletes and updates along with row entries.
NO ACTION : A keyword from standard SQL. In MySQL, equivalent to RESTRICT . The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check.
ON DELETE NO ACTION (which is the same as omitting the ON DELETE clause) will actively prevent deletion of a parent row if it is referenced by any child table, not passively allow it to be deleted without affecting child rows.
Foreign key columns are frequently used in join criteria when the data from related tables is combined in queries by matching the column or columns in the foreign key constraint of one table with the primary or unique key column or columns in the other table.
I think you're misunderstanding what ON DELETE NO ACTION
means. It does not mean to suppress the foreign-key constraint.
When you delete a record that is referred to by a foreign key, InnoDB has the ability to take an automatic action to rectify the situation:
CASCADE
, meaning, delete the referring record. (This would make sense for something like user_address.user_id
. If you hard-delete a user, you probably want to hard-delete all of the user's addresses as well.)SET NULL
, meaning, clear out the referring key. (This might make sense for something like file.last_modified_by
. If you hard-delete a user, you might want the file's last-modified-by to become simply "unknown".)If you specify NO ACTION
, you're telling InnoDB that you don't want it to take either of these actions. So InnoDB can't fix the situation for you; all it can do is reject the DELETE
and return an error.
As a result, ON DELETE NO ACTION
is actually the same as ON DELETE RESTRICT
(the default).
(Note: in some DBMSes, and in standard SQL, ON DELETE NO ACTION
is a bit different from ON DELETE RESTRICT
: in those, ON DELETE NO ACTION
means "accept the DELETE
within the current transaction, but reject the whole transaction if I try to commit it before rectifying the problem". But InnoDB doesn't support deferred checks, so it treats ON DELETE NO ACTION
exactly the same as ON DELETE RESTRICT
, and always rejects the DELETE
immediately.)
See §§14.2.2.5 "FOREIGN KEY Constraints" and 13.1.17.2 "Using FOREIGN KEY Constraints" in the MySQL 5.6 Reference Manual.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With