Managing Foreign Keys in MySQL

A foreign key constraint (also called a referential integrity constraint) designates a column as the foreign key and establishes a relationship between that foreign key and a specified primary or unique key, called the referenced key. The same table or view can contain a foreign key and a referenced key.

You can define multiple foreign keys in a table or view. Also, a single column can be part of more than one foreign key.

The following restrictions are actual for foreign constraints:

  • Both tables must be of the InnoDB type and they must not be temporary tables.
  • In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.
  • In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order. Starting with MySQL/InnoDB 4.1.2, such index will be created on the referenced table automatically if it did not exist.
  • Index prefixes on foreign key columns are not supported. One consequence of this is that BLOB and TEXT columns cannot be included in a foreign key, because indexes on those columns must always include a prefix length.
  • If the CONSTRAINT symbol is given, it must be unique in the database. If it is not given, InnoDB creates the name automatically.

Creating Foreign Keys

1. Open the table you need and choose New Foreign Key on the Table menu.

2. Add required columns, select referenced table and referenced constraint, and click OK.

-or-

1. Switch to the Constraints tab.

2. Create a constraint on the shortcut menu.

Note

Note, to create a foreign key, the referenced table should have a unique index, otherwise dbForge Studio will prompt you to create it. Click Yes in the dialog and the unique index will be added. For more information, see Create New Unique Index Message.

Editing Foreign Keys

1. Navigate to the constraint in Database Explorer, double-click it or open the table that owns the constraint,

2. Switch to the Constraints tab and select Edit Constraint on the shortcut menu.

Dropping Foreign Keys

1. Navigate to Constraints in Database Explorer

2. Select Delete on the shortcut menu.

-or-

1. Open the table that owns the key.

2. Switch to the Constraints tab.

3. Select Delete Constraint on its shortcut menu.

Example of Creating a Foreign Key

Assume you need to ensure that each employee in table Emp (described in the Creating a Database and Two Tables walkthrough) has and will have a valid reference to a manager. That is, for each employee value of field MGR there always will be one of the EMPNO values in the same table. This is best implemented with a foreign key.

  1. Open the table and create a primary key for field EMPNO as described in topic Managing Primary Keys.
  2. Create the foreign key as described above. Select MGR as constraint column, then select EMP as referenced table, and select the primary key you have created in step 1 as the referenced constraint.

That’s all you need to do to establish a referential integrity constraint.