MySQL Foreign Key Error 1005 errno 150 primary key as foreign key

by

When creating a foreign key constraint, MySQL requires a usable index on both the referencing table and also on the referenced table. The index on the referencing table is created automatically if one doesn’t exist, but the one on the referenced table needs to be created manually (Source). Yours appears to be missing.

Test case:

CREATE TABLE tbl_a (
    id int PRIMARY KEY,
    some_other_id int,
    value int
) ENGINE=INNODB;
Query OK, 0 rows affected (0.10 sec)

CREATE TABLE tbl_b (
    id int PRIMARY KEY,
    a_id int,
    FOREIGN KEY (a_id) REFERENCES tbl_a (some_other_id)
) ENGINE=INNODB;
ERROR 1005 (HY000): Can't create table 'e.tbl_b' (errno: 150)

But if we add an index on some_other_id:

CREATE INDEX ix_some_id ON tbl_a (some_other_id);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

CREATE TABLE tbl_b (
    id int PRIMARY KEY,
    a_id int,
    FOREIGN KEY (a_id) REFERENCES tbl_a (some_other_id)
) ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)

This is often not an issue in most situations, since the referenced field is often the primary key of the referenced table, and the primary key is indexed automatically.

More Related Contents:

  1. Calculate date range between two rows
  2. Authentication plugin ‘caching_sha2_password’ cannot be loaded
  3. Error Code: 2013. Lost connection to MySQL server during query
  4. String concatenation in MySQL
  5. MySQL: How to allow remote connection to mysql
  6. Access Denied for User ‘root’@’localhost’ (using password: YES) – No Privileges?
  7. Error Code: 1005. Can’t create table ‘…’ (errno: 150)
  8. MySQL error code: 1175 during UPDATE in MySQL Workbench
  9. Cannot connect to Database server (mysql workbench)
  10. How to connect mysql workbench to running mysql inside docker?
  11. MySQL Workbench: How to keep the connection alive
  12. How do I retrieve my MySQL username and password?
  13. MySQL 8.0.11 error connect to caching_sha2_password the specified module could not be found
  14. MySQL error 1022 when creating table
  15. Is there a naming convention for MySQL?
  16. Can’t connect to MySQL server on ‘127.0.0.1’ (10061) (2003)
  17. MySQL Errno 150
  18. MySQL Workbench: Error in query (1064): Syntax error near ‘VISIBLE’ at line 1
  19. error code 1292 incorrect date value mysql
  20. Order by FIELD in MYSQL
  21. SELECT INTO Variable in MySQL DECLARE causes syntax error?
  22. MySQL. Can’t create table errno 150
  23. Accessing a mysql database from external host/ip? (ie: mysql workbench)
  24. MySQL “ERROR 1005 (HY000): Can’t create table ‘foo.#sql-12c_4’ (errno: 150)”
  25. MySQL errorno 121
  26. Error Code: 1062. Duplicate entry ‘1’ for key ‘PRIMARY’
  27. mySQL query to search all tables within a database for a string?
  28. Create a new database with MySQL Workbench
  29. Errno 121, duplicate key on write or update?
  30. UPDATE with ORDER BY and LIMIT not working in MYSQL

Leave a Comment