Why I cannot successfully create the foreign keys?
Apply OS: iOS
Apply Navicat Product: Navicat for MySQL, Navicat for PostgreSQL
Apply Navicat Version No.: All
MySQL & MariaDB
If you receive MySQL Error likes 1005: Can't create table '.\mydb\#sql-44c_1c.frm'(errno: 150), it is caused by failure on creating Foreign Keys. There are some possible cases that might cause failure on creating Foreign Keys on MySQL database. These errors are related to MySQL itself.
- If the two fields (Field name and the Foreign Field name) are using incompatible field type.
- If you use "On Delete Set Null" but the field doesn't allow null.
To declare foreign keys in MySQL, there are few points which user should bear in mind:
- Both tables must be InnoDB type.
- In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
- Index prefixes on foreign key columns are not supported.
- InnoDB needs indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.
- The two key fields must have the compatible field type.
- The size and the sign of integer types has to be the same.
- The length of string types need not be the same.
- The foreign key name must be unique within the database
- If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.
For more details on Foreign Key Constraints, please visit - http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
If you receive PostgreSQL Error likes ERROR: there is no unique constraint matching given keys for referenced table "xxxx", it is caused by failure on creating Foreign Keys.
To declare foreign keys in PGSQL, there are few points which user should bear in mind:
- A FOREIGN KEY constraint must refer to a PRIMARY KEY or UNIQUE constraint.
- The two key fields must have the compatible data type.
- Must have REFERENCES privilege on both the referencing and referenced tables.