Why Relation Cannot Be Created?
Relations on the diagram correspond to existing foreign keys in a database. When you create a relation on the diagram, a foreign key constraint is created in the database. If the foreign key cannot be created in the database, the relation cannot be created on the diagram.
Possible reasons of this error are the following:
- This version of MySQL server does not support foreign keys. MySQL supports foreign keys from version 3.23.44 and higher.
- Table engine of one or both tables does not support foreign keys. dbForge Studio for MySQL supports foreign keys only if both tables are InnoDB type and they are not temporary tables.
Good to Know
- The column type of the column(s) in a parent table must be identical to the column type of the column(s) in the child table. You must manually create the index on the foreign key column(s).
- You do not need to name the constraint. InnoDB will assign a system name.
- You can enforce foreign key constraints across databases. It is possible to enforce a number of foreign key constraints in a single composite alter table statement.
- MySQL implements an enhanced form of the alter table statement to allow for the optimization migration scripts to large tables.
- The SHOW INNODB STATUS command contains much information about the enforcement of referential integrity.