Why I cannot successfully create the foreign keys?

From Navicat Wiki

Jump to: navigation, search

Apply OS : Windows, Mac, Linux
Apply Navicat Product : Navicat for MySQL, Navicat for PostgreSQL, Navicat for SQLite, Navicat for Oracle, Navicat Premium
Apply Navicat Version No. : All

MySQL

If you recevie 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.

Example cases :

1. If the two fields (Field name and the Foreign Field name) are using incompatible field type.
2. 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 :

1. Both tables must be InnoDB type.
2. In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
3. Index prefixes on foreign key columns are not supported.
4. InnoDB needs indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.
5. The two key fields must have the compatible field type.
6. The size and the sign of integer types has to be the same.
7. The length of string types need not be the same.
8. The foreign key name must be unique within the database
9. 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

PostgreSQL

If you recevie 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 :

1. A FOREIGN KEY constraint must refer to a PRIMARY KEY or UNIQUE constraint.
2. The two key fields must have the compatible data type.
3. Must have REFERENCES privilege on both the referencing and referenced tables.

Oracle

To declare foreign keys in Oracle, there are few points which user should bear in mind :

1. A FOREIGN KEY constraint must refer to a PRIMARY KEY or UNIQUE constraint.
2. The two key fields must have the compatible data type.
3. Composite foreign keys are limited to 32 columns.
4. Must have privileged access to the parent and child tables.

For more details on Foreign Key Constraints, please visit - http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_co.htm#1006976

SQLite

To declare foreign keys in SQLite, there are few points which user should bear in mind :

1. The parent and child keys must have the same cardinality

For more details on Foreign Key Constraints, please visit - http://www.sqlite.org/foreignkeys.html

source : http://wiki.navicat.com/wiki/index.php/Why_I_cannot_successfully_create_the_foreign_keys%3F