mysql不能创建表怎么办,MySQL：无法创建表

我尝试使用下面的CREATE TABLE语句在MySQL中创建一个表：

CREATE TABLE `visit` (

`visit_id` int(11) NOT NULL,

`site_id` int(11) DEFAULT NULL,

PRIMARY KEY (`visit_id`),

CONSTRAINT `FK_visit_site` FOREIGN KEY (`site_id`) REFERENCES `site` (`site_id`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我收到了这个错误：

ERROR 1005 (HY000): Can’t create table ‘fooschema.visit’ (errno: 121)

我使用了SHOW ENGINE INNODB STATUS命令.这是错误消息：

------------------------

LATEST FOREIGN KEY ERROR

------------------------

140222 7:03:17 Error in foreign key constraint creation for table `fooschema`.`visit`.

A foreign key constraint of name `fooschema/FK_visit_site`

already exists. (Note that internally InnoDB adds 'databasename/'

in front of the user-defined constraint name).

Note that InnoDB's FOREIGN KEY system tables store

constraint names as case-insensitive, with the

MySQL standard latin1_swedish_ci collation. If you

create tables or databases whose names differ only in

the character case, then collisions in constraint

names can occur. Workaround: name your constraints

explicitly with unique names.

然后,我使用下面的查询列出所有可用的约束：

select *

from information_schema.table_constraints

where constraint_schema = 'fooschema'

我没有在结果中看到名称为“FK_visit_site”的任何约束.

FK_visit_site约束是表访问的外键约束.我删除了访问表并尝试重新创建它.

有没有办法我可以删除这个外键约束,即使它所关联的表不存在？

解决方法:

您的外键已经存在,因此要么删除已存在的外键,要么重命名您的第二个键.

ALTER TABLE `site` DROP FOREIGN KEY `FK_visit_site`;

或重命名为其他新的.

CREATE TABLE `visit` (

`visit_id` int(11) NOT NULL PRIMARY KEY,

`site_id` int(11) NOT NULL,

CONSTRAINT `FK_visit_site` FOREIGN KEY (`site_id`) REFERENCES `site` (`site_id`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

为visit_id行添加了PRIMARY KEY.

注意：

确保站点表中的site_id在访问表中具有完全相同的site_id数据类型.

像那样

`site_id` int(11) DEFAULT NULL --//in the `site` table

The two keys you’re coupling must have the exact same datatype ( INT NOT NULL), even signedness

标签：mysql,sql,constraints

来源： https://codeday.me/bug/20190708/1406012.html