将外键添加到现有表会导致错误 1050 表已存在

2022-01-20 00:00:00 foreign-keys mysql mysql-workbench mysql-error-1050

我有一个包含列的表 CustomizationSet:

I've a table CustomizationSet with the columns:

customization_set_guid (which is a non-nullable guid and also the primary key)
creator_account_guid
and a few others

和一个带有现有数据的表与列注册:

And a table with existing data Registration with the columns:

registration_id (an int and the primary key)
customization_set_guid (also a guid (so a char(36)) which is nullable, and all entries are currently null)
and a few other columns

当我尝试运行时

ALTER TABLE Registration ADD FOREIGN KEY 
    (
        customization_set_guid
    ) REFERENCES CustomizationSet (
        customization_set_guid
    );

在 MySQL Workbench 中，它给出错误 1050Table '.dbnameegistration' 已存在.

in MySQL Workbench, it gives the error 1050Table '.dbnameegistration' already exists.

如果我尝试使用 UI 通过 Alter Table 对话框的 Foreign Keys 选项卡添加外键，并选择 CustomizationSet 作为引用表，它不允许我在列列表中选择 customization_set_guid.

If I try to use the UI to add the foreign keys with the Foreign Keys tab of the Alter Table Dialog, and choose CustomizationSet as the referenced table, it doesn't let me choose customization_set_guid in the list of columns.

我真的不知道为什么它不允许我添加这个外键.我刚刚在刚刚添加的表之间成功创建了外键.注册表已经存在了一段时间...

I'm really not sure why it won't let me add this foreign key. I've just successfully created foreign keys between tables I just added. The Registration table has existed for awhile...

推荐答案

所以一个团队成员想出了这个.一张表设置为 utf8_general 类型，另一张表设置为默认类型.我不认为这是一个问题，因为默认值是 utf8_general，但显然 mysql 只查看类型名称而不是底层类型.

So a team member figured this out. The one table was set with the type utf8_general, and another was set to the type default. I didn't think this was an issue, since the default is utf8_general, but apparently mysql just looks at the type names and not the underlying type.

相关文章