Entry

Database:MySQL:Key:Foreign:Error: 1005:(HY000): Can't create table .\database\table.frm (errno: 150)

May 22nd, 2005 04:25
Knud van Eeden,

----------------------------------------------------------------------
--- Knud van Eeden --- 22 May 2021 - 04:38 pm ------------------------
Database:MySQL:Key:Foreign:Error: 1005:(HY000): Can't create 
table .\database\table.frm (errno: 150)
---
1. The table to which the foreign key references should
   (already) exist
---
   1. e.g. This will give the error, because 'table2' is currently
      still not created.
--- cut here: begin --------------------------------------------------
-----------------------------------------------
    CREATE TABLE
     table1
    (
     columnNr INT PRIMARY KEY AUTO_INCREMENT,
     columnForeignKey INT,
     INDEX ( columnForeignKey ),
     FOREIGN KEY ( columnForeignKey ) REFERENCES table2 ( columnNr )
    )
   TYPE = INNODB
;
-----------------------------------------------
--- cut here: end ----------------------------------------------------
   2. That this is true, you can check by referring to
      the table itself (that is 'table1', which then already exists,
      as you just are creating it), that SQL query works fine.
--- cut here: begin --------------------------------------------------
-----------------------------------------------
    CREATE TABLE
     table1
    (
     columnNr INT PRIMARY KEY AUTO_INCREMENT,
     columnForeignKey INT,
     INDEX ( columnForeignKey ),
     FOREIGN KEY ( columnForeignKey ) REFERENCES table1 ( columnNr )
    )
   TYPE = INNODB
;
-----------------------------------------------
--- cut here: end ----------------------------------------------------
   3. What you could do if you really want this foreign key
      relation is to
      1. first create the table without this foreign keys.
      2. Then after that tables exists, you ALTER its structure
         and include the FOREIGN KEYS.
          ALTER TABLE table1 ADD FOREIGN KEY( columnForeignKey ) 
REFERENCES table2 ( columnNr );
          or also
          ALTER TABLE table1 ADD FOREIGN KEY( columnForeignKey ) 
REFERENCES table2 ( columnNr ) ON DELETE CASCADE;
      3. All together
--- cut here: begin --------------------------------------------------
-----------------------------------------------
DROP TABLE IF EXISTS table1;
-----------------------------------------------
DROP TABLE IF EXISTS table2;
-----------------------------------------------
CREATE TABLE
 table1
(
 columnNr INT PRIMARY KEY AUTO_INCREMENT,
 columnForeignKey INT,
 INDEX ( columnForeignKey )
)
TYPE = INNODB
;
-----------------------------------------------
CREATE TABLE
 table2
(
 columnNr INT PRIMARY KEY AUTO_INCREMENT
)
TYPE = INNODB
;
-----------------------------------------------
ALTER TABLE
 table1
ADD
 FOREIGN KEY ( columnForeignKey ) REFERENCES table2 ( columnNr )
;
-----------------------------------------------
--- cut here: end ----------------------------------------------------
---
2. The tables should all be of the InnoDb type
3. All foreign keys should have an index
4. Check the format of your foreign key creation in your
   CREATE TABLE
   1. It should be similar to this:
--- cut here: begin --------------------------------------------------
 FOREIGN KEY ( columnYourForeignKey ) REFERENCES tableYourMasterName ( 
columnYourMasterPrimaryKey )
--- cut here: end ----------------------------------------------------
---
5. Make sure that the foreign key fieldname is written correctly and 
exists
6. Make sure the field types match
    e.g.
     if the foreign key in the first table is of type INTEGER, then
     the field it points to in the other table should also be of the 
type INTEGER.
---
---
Internet: see also:
---
[Internet: http://www.google.com search for 'ERROR 1005 (HY000): Can 
not create table .frm'(errno: 150)': http://forums.mysql.com/read.php?
25,15717,25969]
---
Database: Relational: MySql: Link: Overview: Can you give an overview 
of links?
http://www.faqts.com/knowledge_base/view.phtml/aid/35331/fid/52
----------------------------------------------------------------------