Tipos de relaciones de Foreign key en mysql y phpmyadmin y ejemplo de sintaxis

Los tipos de relaciones que pueden establecerse vienen definido por:

(fuente: http://stackoverflow.com/questions/459312/setting-up-foreign-keys-in-phpmyadmin)

CASCADE

Whenever rows in the master (referenced) table are deleted (resp. updated), the respective rows of the child (referencing) table with a matching foreign key column will get deleted (resp. updated) as well. This is called a cascade delete (resp. update[2]).

RESTRICT

A value cannot be updated or deleted when a row exists in a foreign key table that references the value in the referenced table. Similarly, a row cannot be deleted as long as there is a reference to it from a foreign key table.

NO ACTION

NO ACTION and RESTRICT are very much alike. The main difference between NO ACTION and RESTRICT is that with NO ACTION the referential integrity check is done after trying to alter the table. RESTRICT does the check before trying to execute the UPDATE or DELETE statement. Both referential actions act the same if the referential integrity check fails: the UPDATE or DELETE statement will result in an error.

SET NULL

The foreign key values in the referencing row are set to NULL when the referenced row is updated or deleted. This is only possible if the respective columns in the referencing table are nullable. Due to the semantics of NULL, a referencing row with NULLs in the foreign key columns does not require a referenced row.

SET DEFAULT

Similar to SET NULL, the foreign key values in the referencing row are set to the column default when the referenced row is updated or deleted.


Ejemplo de sintaxis FK:


ALTER TABLE  `usuarios_planes` ADD FOREIGN KEY (  `fk_plan` ) REFERENCES  `planes` (

`id`
) ON DELETE RESTRICT ON UPDATE RESTRICT ;