Monday, October 1, 2012

Foreign Key in MySQL


Foreign Keys are a way of implementing relationships/constraints between columns in different tables. For example, we want to make sure that the value of the county column in the towns table has an associated entry in the counties table. In this way, no-one can place a town in a non-existent county and similarly no one can remove a county and leave orphaned towns behind.

We refer to the towns table as the child and the counties table as the parent of world database.

There are different categories of constraints that influence how they’re enforced when a row is updated or deleted from the parent table:

  • Cascade: If a row is deleted from the parent then any rows in the child table with a matching FK value will also be deleted. Similarly for changes to the value in the parent table.
  • Restrict: A row cannot be deleted from the parent table if this would break a FK constraint in the child table. Similarly for changes to the value in the parent table.
  • No Action: Very similar to “Restrict” except that any events/triggers on the parent table will be executed before the constraint is enforced – giving the application writer the option to resolve any FK constraint conflicts using a stored procedure.
  • Set NULL: If NULL is a permitted value for the FK column in the child table then it will be set to NULL if the associated data in the parent table is updated or deleted.
  • Set Default: If there is a default value for the FK column in the child table then it will be used if the associated data in the parent table is updated or deleted. Note that this is not implemented in this version.

1 comment:

Sai Reddy said...

Hi . I had a plan to get trained on MySQL so can you share your gmail id.