FK In MySQL and SQLyog

No matter what table type (of those supporting FK's) are used for defining the FK relationship/constraint, both tables involved in the relationship have to be same type and there must be an index where the foreign key and the referenced key are listed as the first columns. MySQL does not always auto-create indexes on foreign keys or referenced keys: sometimes you have to create them explicitly. It depends on the MySQL version.

Corresponding columns in the foreign key and the referenced key must have similar internal data types so that they can be compared without a type conversion. The size and the signedness of integer types have to be the same. The length of string types need not be the same.

Starting from MySQL version 3.23.50 you can also associate the ON DELETE CASCADE or ON DELETE SET NULL clause with the foreign key constraint. If ON DELETE CASCADE is specified, and a row in the parent table is deleted, then InnoDB automatically deletes also all those rows in the child table whose foreign key values are equal to the referenced key value in the parent row. If ON DELETE SET NULL is specified, the child rows are automatically updated so that the columns in the foreign key are set to the SQL NULL value. Corresponding ON UPDATE options are available starting from 4.0.8.

FK in SQLyog

SQLyog treats one of the tables in the relationship as the child and the other parent. Using SQLyog you create relationships on the child table that will refer to the columns of the parent table.

First of all you need to create correct indexes on the columns of both the tables. You can do this from the Index tab.

Select the Child table in the Object Browser and press F10 or select Relationships/Foreign Keys from the popup menu or click on the foreign keys subtab in the Create Table tab.

Note: If the table is not of InnoDB, Soliddb or PBXT types, SQLyog will give an error…You can change the handler of table by selecting it from the Table Engine combo box.