创建表失败，外键约束不正确地形成 [英] Create table fails with Foreign Key Constraint is incorrectly Formed

问题描述

MariaDB InnoDB外键问题

MariaDB InnoDB Foreign Key Issue

想要开始说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.

我有一个表在我的数据模型中的多个表的中心。所以，沿着这些线：

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;

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

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

相关表格示例如下

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;

问题

ATHING表包括ON DELETE SET NULL。保存，修改一切似乎确定。我正在使用HeidiSQL来执行此操作。

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.

长故事我在拖着我的表和低的列表，看看我的USERS表是GONE！通过大量的阅读和努力，我能够清理干净，但感觉真的确保事情是好的我放弃了所有的FK指向USERS表，并删除了表。

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表时，我收到此错误：

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

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

我注意到，我的第一次尝试这样做是因为我认为我会删除所有的FK，有键的残余仍然在那里特定的索引，支持这些键在一些表。在查询INNODB_SYS_TABLES和INNODB_SYS_INDEXES表时，我认为被删除的那些索引仍然存在于这些系统表中。

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.

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

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?

推荐答案

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

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:

• int li>
  
• int 列有不同的标志（sans AUTO_INCREMENT）

• The int columns have different sizes
• The int columns have different flags (sans AUTO_INCREMENT)

作为一个例子，我创建了一个工具，用某种方式创建一个表 INT（10）而不是预期的 INT （11）。即使我在创建两个时只选择 INT ，它也被搞砸 - 从来没有跟踪为什么。

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

短，通常最好在创建表时显式地指定 INT 大小。

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

在您的情况下，应该工作：

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;

这篇关于创建表失败，外键约束不正确地形成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！