错误:错误1215:不能添加外键约束

I get error: ERROR: Error 1215: Cannot add foreign key constraint

我得到错误:错误:错误1215:不能添加外键约束

I read other similar topics and http://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html obviously however I can't fix the issue.

我阅读了其他类似的主题和http://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html，但显然我无法解决这个问题。

The Code is generated as follows (and it should work flawlessly god damn it):

代码的生成如下(它应该可以完美地工作，该死的):

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `foilmaster` DEFAULT CHARACTER SET latin1 ;
USE `foilmaster`;

-- -----------------------------------------------------
-- Table `foilmaster`.`Company`
-- -----------------------------------------------------

DROP TABLE IF EXISTS `foilmaster`.`Company` ;

CREATE  TABLE IF NOT EXISTS `foilmaster`.`Company` (
    `idCompany` MEDIUMINT(9) NOT NULL AUTO_INCREMENT ,
    `Name` VARCHAR(85) NOT NULL ,
    `Country` VARCHAR(35) NOT NULL ,
    `PotentialSaleScale` SMALLINT NOT NULL COMMENT '1-100 Where 1 is 1 Pallete a Month and 100 is 1 Truck a week' ,
    `DateAdded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    PRIMARY KEY (`idCompany`)
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `foilmaster`.`ContactData`
-- -----------------------------------------------------

DROP TABLE IF EXISTS `foilmaster`.`ContactData` ;

CREATE  TABLE IF NOT EXISTS `foilmaster`.`ContactData` (
    `idCompany` MEDIUMINT(9) NOT NULL ,
    `Phone` VARCHAR(45) NOT NULL ,
    INDEX `fk_ContactData_Company_idx` (`idCompany` ASC) ,
    PRIMARY KEY (`idCompany`) ,
    CONSTRAINT `fk_ContactData_Company` FOREIGN KEY (`idCompany` )
        REFERENCES `foilmaster`.`Company` (`idCompany` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
    ) ENGINE = InnoDB;

USE `foilmaster` ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

InnoDB status shows this:

InnoDB状态显示:

=====================================
2013-05-26 13:07:32 460 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 52 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 8 srv_active, 0 srv_shutdown, 7450 srv_idle
srv_master_thread log flush and writes: 7458
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 20
OS WAIT ARRAY INFO: signal count 19
Mutex spin waits 2, rounds 0, OS waits 0
RW-shared spins 19, rounds 570, OS waits 19
RW-excl spins 1, rounds 30, OS waits 1
Spin rounds per wait: 0.00 mutex, 30.00 RW-shared, 30.00 RW-excl
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2013-05-26 13:07:16 f0c Error in foreign key constraint of table foilmaster/contactdata:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
  CONSTRAINT "ID" FOREIGN KEY ("ID") REFERENCES "company" ("ID")
The index in the foreign key in table is "PRIMARY"
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
------------
TRANSACTIONS
------------
Trx id counter 3386
Purge done for trx's n:o < 3386 undo n:o < 0 state: running but idle
History list length 240
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 12, OS thread handle 0x460, query id 394 localhost 127.0.0.1 root init
SHOW ENGINE INNODB STATUS
---TRANSACTION 0, not started
MySQL thread id 2, OS thread handle 0x1560, query id 83 localhost 127.0.0.1 root cleaning up
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
405 OS file reads, 168 OS file writes, 82 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.37 writes/s, 0.17 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 58207, node heap has 0 buffer(s)
0.00 hash searches/s, 0.81 non-hash searches/s
---
LOG
---
Log sequence number 8995932
Log flushed up to   8995932
Pages flushed up to 8995932
Last checkpoint at  8995932
0 pending log writes, 0 pending chkp writes
43 log i/o's done, 0.08 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 30048256; in additional pool allocated 0
Dictionary memory allocated 55489
Buffer pool size   1792
Free buffers       1369
Database pages     423
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0 single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 391, created 32, written 97
0.00 reads/s, 0.08 creates/s, 0.21 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 423, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread id 2672, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Spent 2 hours on that so far.... just staring :P

花了2个小时,到目前为止....只是盯着:P

1 个解决方案

#1

Both answers were wrong as it turns out. What was the solution?

事实证明，这两个答案都是错的。解决方案是什么?

I moved this line up to the top DROP TABLE IF EXISTS foilmaster.ContactData ;

如果存在foilmaster，我将这一行移动到顶部删除表。ContactData;

Also, ticking the check box DROP schema also works.

此外，勾选复选框下的模式也可以工作。

I started wondering why it is the way it is... and ended up with the usual "I'm fine as long as it works" :)

我开始奇怪为什么会这样……最后，他还是像往常一样说:“只要它管用，我就没事。”