Search code examples
mysqlforeign-keysnavicat

How to check if a foreign key is created

I have created a foreign key with NaviCat (a MySQL application), but the instant I create it, it disappears from the foreign key list and a new Index get added. Does that mean something went wrong or is that normal to happen?

I have tried using the information_schema How to check if a column is already a foreign key? but that resulted in Unknown table 'REFERENTIAL_CONSTRAINTS' in information_schema. Is it possible that query is for MsSQL and is different with MySQL?

Solution

    1. The table you are probably creating the foreign key is MyISAM. Go to the Table Design view and go to the Options tab to change the table Engine to InnoDB

    2. You can change all your tables to InnoDB following the steps at http://kevin.vanzonneveld.net/techblog/article/convert_all_tables_to_innodb_in_one_go/

    3. Add default-storage-engine=innodb in the [mysqld] section of your MySQL configuration file (usually my.cnf) from http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html

    There are no flaws after all from MySQL 5.5 InnoDB is the default storage engine.