MySQL 创建表失败，外键约束格式不正确

Create table fails with Foreign Key Constraint is incorrectly Formed

提问by John

Topic

话题

MariaDB InnoDB Foreign Key Issue

MariaDB InnoDB 外键问题

Want to start off by saying I'm new to InnoDB and spent all day reading posts yesterday I've tried multiple things along the way to get me where I am now so am I hosed or is there a way out of this dark forest.

我想首先说我是 InnoDB 的新手，昨天花了一整天的时间阅读帖子，一路上我尝试了多种方法来让我到达现在的位置，所以我被冲洗掉了，或者有办法摆脱这片黑暗的森林。

I have a table that is central to a number of tables in my data model. So something along these lines:

我有一个表，它是我的数据模型中许多表的核心。所以沿着这些路线：

create table users (id int not null auto_increment
, username varchar(255) NOT NULL
, password varchar(255) NOT NULL
, active int NOT NULL
, PRIMARY KEY (id))
ENGINE=InnoDB COLLATE=utf8_unicode_ci;

Decided to clean up some DELETE / UPDATE clauses on my FKs quickly this weekend...Famous Last Words...

决定在本周末快速清理我的 FK 上的一些 DELETE / UPDATE 子句......著名的遗言......

A related table example is here

一个相关的表示例是here

create table athing (id int not null auto_increment
, name varchar(255) not null
, status varchar(255) not null
, created_by_user_id int 
, PRIMARY KEY (id)
, CONSTRAINT athing_fk1 FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB COLLATE=utf8_unicode_ci;

Problem

问题

Modified the FK in the "ATHING" table to include ON DELETE SET NULL. Saved that modification everything seemed ok. I was using HeidiSQL to perform this.

修改了“ATHING”表中的 FK 以包含 ON DELETE SET NULL。保存了修改一切似乎没问题。我正在使用 HeidiSQL 来执行此操作。

Long story short I was trolling through my list of tables and low and behold my USERS table was GONE! Through a lot of reading and effort I was able to get things cleaned up but felt to really ensure things were good I dropped all FKs pointing at USERS table and dropped the table.

长话短说，我正在浏览我的表列表，然后发现我的 USERS 表不见了！通过大量的阅读和努力，我能够把事情清理干净，但我觉得真的确保一切都很好，我把所有指向 USERS 表的 FK 都扔掉了，然后扔掉了桌子。

Now when I attempt to re-create the USERS table I receive this error:

现在，当我尝试重新创建 USERS 表时，我收到此错误：

ERROR 1005 (HY000): Can't create table `sprintdb`.`system_users` (errno: 150 "Foreign key constraint is incorrectly formed")

What I noticed post my first attempt at doing this is while I'd thought I'd dropped all FKs there were remnants of keys still out there specifically indexes that supported those keys on some of the tables. In querying the INNODB_SYS_TABLES and INNODB_SYS_INDEXES tables that those indexes that I thought were removed still exist in these system tables.

在我第一次尝试这样做时，我注意到的是，虽然我认为我已经放弃了所有 FK，但仍有剩余的键，特别是在某些表上支持这些键的索引。在查询 INNODB_SYS_TABLES 和 INNODB_SYS_INDEXES 表时，我认为已删除的那些索引仍然存在于这些系统表中。

Is there a way to move beyond this I feel like there exists some piece of information somewhere whether it be in the file system or in the database itself that needs to be refreshed or removed so that I can move forward...thoughts?

有没有办法超越这个我觉得在某处存在一些信息，无论是在文件系统中还是在数据库本身中，需要刷新或删除，以便我可以继续前进......想法？

回答by h0tw1r3

I have received this message many times while using 3rd party tools to create tables and then constrain against existing tables. It's either one of two things:

我在使用 3rd 方工具创建表然后约束现有表时多次收到此消息。它是以下两种情况之一：

• The intcolumns have different sizes
• The intcolumns have different flags (sans AUTO_INCREMENT)

• 该int列具有不同的尺寸
• 该int列有不同的标志（SANS AUTO_INCREMENT）

As an example, I created a table with a tool that somehow created a column as INT(10)instead of the expected INT(11). Even though I just chose INTwhen creating both, it was messed up - never tracked down why.

例如，我使用一个工具创建了一个表，该工具以某种方式创建了一个列INT(10)而不是预期的INT(11). 尽管我只是INT在创建两者时选择了它，但它搞砸了 - 从来没有追查过原因。

Long story short, it's generally best to explicitly state the INTsize when creating a table.

长话短说，通常最好INT在创建表时明确说明大小。

In your case, the following should work:

在您的情况下，以下应该有效：

create table users (id int(11) not null auto_increment
, username varchar(255) NOT NULL
, password varchar(255) NOT NULL
, active int NOT NULL
, PRIMARY KEY (id))
ENGINE=InnoDB COLLATE=utf8_unicode_ci;

create table athing (id int(11) not null auto_increment
, name varchar(255) not null
, status varchar(255) not null
, created_by_user_id int(11) not null
, PRIMARY KEY (id)
, CONSTRAINT athing_fk1 FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB COLLATE=utf8_unicode_ci;