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

2022-01-15 00:00:00 database foreign-key-relationship mariadb mysql

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...

这里有一个相关的表格示例

A related table example is 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;

问题

修改了ATHING"表中的 FK 以包含 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 桌子不见了！通过大量的阅读和努力，我能够把事情清理干净，但我觉得要真正确保事情是好的，我放弃了所有指向 USERS 表的 FK 并放弃了表.

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?

推荐答案

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

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 列的大小不同
  • int 列具有不同的标志(无 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 column as 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;

相关文章