将外键添加到现有的表给出错误1050表已经存在 [英] Adding foreign key to existing table gives error 1050 table already exists

问题描述

  customization_set_guid（这是一个不可为空的guid，也是主要的键）
 creator_account_guid 
和其他几个
  

  registration_id（一个int和主键）
 customization_set_guid（也是一个guid（so a char（36））是空的，所有的条目都是空的）
和其他一些列
  

$ p

$ p $ ALTER TABLE注册ADD FOREIGN KEY
（
customization_set_guid
）REFERENCES CustomizationSet（
customization_set_guid
）;


在MySQL Workbench中，它给出错误1050Table'.\dbname\registration'已经存在。

如果我尝试使用UI来添加具有Alter Table对话框的Foreign Keys选项卡的外键，并选择CustomizationSet作为引用表，让我在列的列表中选择customization_set_guid。

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

解决方案

所以一个团队成员想通了。一个表被设置为类型utf8_general，另一个被设置为默认类型。我不认为这是一个问题，因为默认是utf8_general，但显然mysql只是查看类型名称而不是基础类型。

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

When I try and run

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

in MySQL Workbench, it gives the error 1050Table '.\dbname\registration' already exists.

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

解决方案

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.

这篇关于将外键添加到现有的表给出错误1050表已经存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！