MySQL.

MySQL. 테이블을 만들 수 없습니다. errno 150

---

MySQL에서 두 개의 테이블이있는 데이터베이스를 만들어야하지만 스크립트가 errno 150 (외래 키 문제)으로 실패합니다. 두 테이블에서 동일한 외래 키 필드를 다시 확인했는데 오류를 찾을 수 없습니다.

다음은 스크립트입니다.

 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;

다른 버전의 MySQL을 사용하여 Windows 및 Ubuntu에서 시도했지만 작동하지 않았습니다.

어떤 아이디어?

---

table1.field1 그것에 정의 된 색인이 없습니다.

에 FOREIGN KEY제약 조건을 지정해야 field1합니다.

이것으로 :

 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;

그러면 모든 것이 예상대로 작동합니다.

---

MySQL Workbench 및 MySQL 5.5.27로 작업하는 동안 비슷한 문제가 발생했습니다. 제 경우에는 INT 유형 필드에 문제가있었습니다. 한 테이블에서는 INT UNSIGNED가 잘못되었고 참조 테이블에서는 INT였습니다.

---

MySQL 버전에 따라 먼저 table1.field1에 인덱스를 만들어야 할 수도 있습니다.

---

여기에 대한 답변 중 하나는 외래 키 무결성 검사를 비활성화하는 것입니다. 이것은 나쁜 생각입니다. 여기에는 두 가지 잠재적 인 범인이 있습니다.

• 참조 된 기본 키와 참조하는 외래 키 간의 데이터 유형 불일치
• 지수. 인덱싱하는 모든 외래 키는 NULL이 아니어야합니다.

---

또 다른 힌트 :

데이터 유형이 동일 해 보일 때도 (제 경우에는 두 열이 모두) VARCHAR(50)이것만으로는 충분하지 않습니다.

또한 두 열이 동일한 COLLATION.

---

다른 원인과 약간 비슷하지만 또 다른 원인 : InnoDB 대신 MyISAM 엔진이있는 것으로 밝혀진 테이블을 참조했습니다.

---

MySQL은 참조 테이블의 이름을 잘못 입력 한 경우에도이 오류를 발생시킵니다. 내가 편지를 놓쳤다는 것을 깨달을 때까지 잠시 머리를 뽑았습니다.foreign key (column1) references mistyped_table(column1)

---

옵션 (케이스에 따라 다름)은 MySQL 무결성 검사를 비활성화하는 것입니다.

SET FOREIGN_KEY_CHECKS = 0;

---

If nothing works, try this:

외래 키 이름은 이미 존재하는 키의 중복입니다. 외래 키의 이름이 데이터베이스 내에서 고유한지 확인하십시오. 이를 테스트하려면 키 이름 끝에 임의의 문자 몇 개를 추가하면됩니다.

---

In my case, one table was using foreign key constraints on another table that didn't exist yet. This was happening due to a large makefile, so it wasn't as obvious as I would've expected.

---

In case somebody is still having problems with this, I tried all the solutions above (except for SET FOREIGN_KEY_CHECKS) and nothing worked. The problem was that when you reference the first table, some databases are case sensitive about the table names. I think this is weird since I never saw this before on MySQL, Oracle and now this happened for me on MariaDB.

For example:

Create table if not exists CADASTRO_MAQUINAS ( Id VARCHAR(16), Primary Key (Id) );

Create table if not exists INFOS ( Id_Maquina VARCHAR(16) NOT NULL, CONSTRAINT FK_infos_cadastro_maquinas Foreign Key (Id_Maquina) references CADASTRO_MAQUINAS(Id) );

If I try to create the second table using cadastro_maquinas (lower cases) instead of CADASTRO_MAQUINAS, I will receive this error.

---

I was using MySQL workBench. THe issue is you cannot use the same foreign key name, they need to be unique. So if more than one table will reference the same foreign key, each time there must be a unique name given.

---

I had a similar error on one of my tables. When checked column Collation was different, which worked once changed both columns to the same Collation type.

After reading most of the suggested solution here. I just thought it might be helpful if I just list down all the possibilities which could throw this error.

1, Check CASE of the Column 2, Check COLLATION of Columns 3, Check if there is a key created in both tables for the column (Unique, Primary)

---

In my case I got old table definition MyISAM in one of the tables and obviously I was unable to make foreign key to it from another table. Maybe this help someone.

So this may happen because of inconsistencies between two databases/fields definitions try to check:

Field Type
Field Collation
Table Engine

---

For me, the problem was with using CONSTRAINT in the CREATE TABLE query.

---

You also may encounter the same error when attempting to reference a composite key in your foreign key.

For example:

CREATE TABLE `article` (
  `id` int(10) unsigned NOT NULL,
  `type` enum('X','Y','Z') NOT NULL,
  PRIMARY KEY (`id`,`type`)
) ENGINE InnoDB;

CREATE TABLE `t1` (
  `user_id` int(10) unsigned NOT NULL,
  `type` enum('X','Y','Z') NOT NULL,
  `article_id` int(10) unsigned NOT NULL,
  CONSTRAINT `user_access_article_ibfk_2` FOREIGN KEY (`article_id`, `type`) REFERENCES `article` (`id`, `type`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

In this case, it is important to use article_id and type field in the FK definition in the very same order as they appear in the article table PRIMARY KEY definition.

---

In my case was probably a server bug dropping a table with the same name. Dropping the whole shcema and re-creating it solved the problem.

---

In very strange cases your database might be broken. In my case I did not have any foreign keys on the table and the only renaming the table or changing engine helped.

Turned out innoDB was broken, see: https://dba.stackexchange.com/questions/86853/1025-error-on-rename-of-table-errno-150-table-was-deleted-while-tried-to-a?lq=1

---

If you are working on mysql workbench and you get this error for a relationship table there may be a quick fix for you: just delete it and let mysql workbench recreate it for you. Then copy the sql. Fixed my errno 150 problem.

---

I got this error while trying to use a foreign key to reference a non-unique field. (which apparently is not allowed)

---

When I had this problem it was because I had set the id in the first table to be unsigned whereas the foreign key in the second table was not. Making them both unsigned fixed it for me.

---

Always create master/parent tables first, and then create your detail/child tables.

참고URL : https://stackoverflow.com/questions/1749332/mysql-cant-create-table-errno-150