MySQL的.无法创建表格errno 150 [英] MySQL. Can't create table errno 150
本文介绍了MySQL的.无法创建表格errno 150的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我必须在MySQL中创建一个包含两个表的数据库,但是该脚本因errno 150(外键问题)而失败.我再次检查了两个表上的外键字段是否相同,并且找不到任何错误.
I have to create a database with two tables in MySQL, but the script fails with errno 150 (foreign key problem). I double-checked the foreign key fields to be the same on both tables, and I can't find any error.
这是脚本:
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';
DROP SCHEMA IF EXISTS `testdb`;
CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
USE `testdb`;
DROP TABLE IF EXISTS `testdb`.`table1` ;
CREATE TABLE IF NOT EXISTS `testdb`.`table1` (
`id` INT UNSIGNED NOT NULL ,
`field1` VARCHAR(50) NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
DROP TABLE IF EXISTS `testdb`.`table2` ;
CREATE TABLE IF NOT EXISTS `testdb`.`table2` (
`id` INT NOT NULL AUTO_INCREMENT ,
`field1` VARCHAR(50) NULL ,
`date` DATE NULL ,
`cnt` INT NULL ,
PRIMARY KEY (`id`) ,
INDEX `FK_table2_table1` (`field1` ASC) ,
CONSTRAINT `FK_table2_table1`
FOREIGN KEY (`field1`)
REFERENCES `testdb`.`table1` (`field1` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
我已经在Windows和Ubuntu中使用不同版本的MySQL进行了尝试,但无法正常工作.
I've tried it in Windows and Ubuntu with different versions of MySQL and didn't work.
有什么想法吗?
推荐答案
table1.field1
上没有定义索引.
需要在field1
上放置FOREIGN KEY
约束.
与此:
CREATE TABLE IF NOT EXISTS `testdb`.`table1` (
`id` INT UNSIGNED NOT NULL ,
`field1` VARCHAR(50) NULL ,
KEY ix_table1_field1 (field1),
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
然后一切都会按预期进行.
Everything should then work as expected.
这篇关于MySQL的.无法创建表格errno 150的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文