MySQL错误1215：无法添加外键约束，innodb，新表 [英] MySQL Error 1215: Cannot add foreign key constraint, innodb, new tables

问题描述

我喜欢添加外键，这个表，但不行！如果我尝试新的表不工作，但其他，旧表。有什么问题？请帮助我。

  CREATE TABLE`tanora`（
`idtanora` int（11）NOT NULL，
`tema` varchar（250）NULL，
`megjegyzes` varchar（255）DEFAULT NULL，
`datum` date NOT NULL，
`osztaly` varchar（20）NOT NULL， 
`megtartott` int（11）NOT NULL，
`targy` varchar（45）NOT NULL，
`kezdete` time NOT NULL，
````NOT NOT， 
 PRIMARY KEY（`idtanora`），
 UNIQUE KEY`idtanora_UNIQUE`（`idtanora`），
`` osztazon_idx`（`osztaly`）
）ENGINE = InnoDB DEFAULT CHARSET = latin2 COLLATE = latin2_hungarian_ci; 
 
 
 $ b CREATE TABLE`osztaly`（
`osztalyazon` varchar（20）NOT NULL，
`osztalyfonokazon` varchar（11）NOT NULL， 
`osztalynev` varchar（45）NOT NULL，
`indul` year（4）NOT NULL，
 PRIMARY KEY（`osztalyazon`），
 UNIQUE KEY`osztalyazon_UNIQUE` `osztalyazon`），
 key`ofazon_idx`（`osztalyfonokazon`），
 CONSTRAINT`tanarazon01` FOREIGN KEY（`osztalyfonokazon`）参考`tanar`（`szemelyiszam`）ON UPDATE CASCADE 
 ）ENGINE = InnoDB DEFAULT CHARSET = latin2 COLLATE = latin2_hungarian_ci; 
 
 
 
错误1215：无法添加外键约束
 SQL语句：
 
 ALTER TABLE`enaplo`.`tanora` 
 ADD CONSTRAINT`osztalyra` 
 FOREIGN KEY（`osztaly`）
 REFERENCES`enaplo`.`osztaly`（`osztalyazon`）
 ON DELETE NO ACTION 
 ON UPDATE NO操作
 
 
错误：运行故障恢复脚本时出错。详情如下。 
 
错误1050：表'tanora'已经存在
 SQL语句：
 CREATE TABLE`tanora`（
`idtanora` int（11）NOT NULL，
`tema` varchar（250）NOT NULL，
`megjegyzes` varchar（255）DEFAULT NULL，
`datum` date NOT NULL，
`osztaly` varchar（20）NOT NULL ，
`megtartott` int（11）NOT NULL，
`targy` varchar（45）NOT NULL，
`kezdete` time NOT NULL，
`vege` time NOT NULL ，
 PRIMARY KEY（`idtanora`），
 UNIQUE KEY``idtanora_UNIQUE`（`idtanora`）
）ENGINE = InnoDB DEFAULT CHARSET = latin2 COLLATE = latin2_hungarian_ci 
 
 
 
外键（osztaly）
引用enaplo.osztaly（osztalyazon）
删除无操作
更新无操作：
无法找到索引所引用的表中
引用的列显示为第一列，或者表中的列类型
和引用的表不匹配约束。 
请注意，在> = InnoDB-4.1.12创建的
表中，ENUM和SET的内部存储类型发生了变化，旧表
中的这样的列不能被新的表。有关正确的外键定义，请参阅http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html 
。 
  

什么问题，请帮忙？！

<看起来像你的帖子主要是代码;请添加一些更多details.looks喜欢你的帖子主要是代码;请添加一些更多details.looks喜欢你的帖子主要是代码;请添加一些更多details.looks喜欢你的帖子主要是代码;请添加一些更多details.looks喜欢你的帖子主要是代码;请添加一些更多details.looks喜欢你的帖子主要是代码;请添加一些更多details.looks喜欢你的帖子主要是代码;请添加更多详细信息。

确保您有一致的列定义：

>

在引用表 osztaly 中，外键列 osztalyfonokazon 需要被定义为与应用于 tanar 表中的引用字段完全相同的特征。

目前， osztalyfonokazon 在 osztaly 中定义如下：

 `osztalyfonokazon` VARCHAR（11）NOT NULL 
  

那么在 tanar 表 szemelyiszam 定义应该看起来像

 `szemelyiszam` VARCHAR（11）PRIMARY KEY 
  

或

 `szemelyiszam` VARCHAR（11）UNIQUE KEY NOT NULL 
  

取决于您的需求。

无论哪种方式，请确保 szemelyis zam 是 PRIMARY KEY 或至少是 UNIQUE KEY tanar 中声明 NOT NULL table。

另外，出于性能方面的原因，MySQL要求将被引用的列索引为
。但是，系统不会执行
的要求，即被引用的列是UNIQUE或被声明为NOT
NULL。对包含NULL值的非唯一键或键
的外键引用的处理对于
UPDATE或DELETE CASCADE等操作没有很好的定义。建议您使用外键
只引用UNIQUE（包括PRIMARY）和NOT NULL键。

^源

I like to add foreign keys, this table, but not work! If I try new tables not work, but other, old tables its work. Whats the problem? Pls help me.

CREATE TABLE `tanora` (
`idtanora` int(11) NOT NULL,
`tema` varchar(250) NULL,
`megjegyzes` varchar(255) DEFAULT NULL,
`datum` date NOT NULL,
`osztaly` varchar(20) NOT NULL,
`megtartott` int(11) NOT NULL,
`targy` varchar(45) NOT NULL,
`kezdete` time NOT NULL,
`vege` time NOT NULL,
PRIMARY KEY (`idtanora`),
UNIQUE KEY `idtanora_UNIQUE` (`idtanora`),
KEY `osztazon_idx` (`osztaly`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_hungarian_ci;



CREATE TABLE `osztaly` (
`osztalyazon` varchar(20) NOT NULL,
`osztalyfonokazon` varchar(11) NOT NULL,
`osztalynev` varchar(45) NOT NULL,
`indul` year(4) NOT NULL,
PRIMARY KEY (`osztalyazon`),
UNIQUE KEY `osztalyazon_UNIQUE` (`osztalyazon`),
KEY `ofazon_idx` (`osztalyfonokazon`),
CONSTRAINT `tanarazon01` FOREIGN KEY (`osztalyfonokazon`) REFERENCES `tanar` (`szemelyiszam`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_hungarian_ci;



ERROR 1215: Cannot add foreign key constraint 
SQL Statement:

ALTER TABLE `enaplo`.`tanora` 
ADD CONSTRAINT `osztalyra`
FOREIGN KEY (`osztaly`)
REFERENCES `enaplo`.`osztaly` (`osztalyazon`)
ON DELETE NO ACTION
ON UPDATE NO ACTION


ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'tanora' already exists
SQL Statement:
CREATE TABLE `tanora` (
 `idtanora` int(11) NOT NULL,
 `tema` varchar(250) NOT NULL,
 `megjegyzes` varchar(255) DEFAULT NULL,
 `datum` date NOT NULL,
 `osztaly` varchar(20) NOT NULL,
 `megtartott` int(11) NOT NULL,
 `targy` varchar(45) NOT NULL,
 `kezdete` time NOT NULL,
 `vege` time NOT NULL,
PRIMARY KEY (`idtanora`),
UNIQUE KEY `idtanora_UNIQUE` (`idtanora`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_hungarian_ci



foreign key (osztaly)
references enaplo.osztaly (osztalyazon)
on delete no action
on update no action:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

Whats the problem, pls help?!

looks like your post is mostly code; please add some more details.looks like your post is mostly code; please add some more details.looks like your post is mostly code; please add some more details.looks like your post is mostly code; please add some more details.looks like your post is mostly code; please add some more details.looks like your post is mostly code; please add some more details.looks like your post is mostly code; please add some more details.

解决方案

Make sure that you have consistent column definitions:

In your referencing table osztaly the foreign key column osztalyfonokazon needs to be defined with the exact same characteristics as applied to the referenced field in the tanar table.

Currently, osztalyfonokazon in osztaly is defined as follows:

`osztalyfonokazon` VARCHAR(11) NOT NULL

So, then in the tanar table the szemelyiszam definition should look like either

`szemelyiszam` VARCHAR(11) PRIMARY KEY

or

`szemelyiszam` VARCHAR(11) UNIQUE KEY NOT NULL

Depends on your needs.

Either way, ensure that szemelyiszam is a PRIMARY KEY or at least a UNIQUE KEY that is declared NOT NULL in the referenced tanar table.

Additionally, MySQL requires that the referenced columns be indexed for performance reasons. However, the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL. The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys.

^Source

这篇关于MySQL错误1215：无法添加外键约束，innodb，新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！