mysql添加添加两次_MySQL – 添加约束一次用不同的情况添加两次-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_33275503/article/details/113669437

在尝试向MySQL表添加外键约束时遇到重复约束的问题，使用相同的约束名称两次导致了奇怪的行为。通过调整约束名称成功避免了该问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

当我向

MySQL表添加约束时,我有这种奇怪的行为：

[...] add constraint qrtz_blob_triggers_ibfk_1 foreign key [...]

现在SHOW CREATE TABLE显示两次约束：

CONSTRAINT `qrtz_blob_triggers_ibfk_1` FOREIGN KEY [...]

CONSTRAINT `QRTZ_BLOB_TRIGGERS_ibfk_1` FOREIGN KEY [...]

这是完整的会话：

mysql> DROP DATABASE IF EXISTS my_test; CREATE DATABASE my_test default character set utf8 collate utf8_bin;

Query OK, 2 rows affected (0.07 sec)

Query OK, 1 row affected (0.00 sec)

mysql> use my_test;

Database changed

mysql>

mysql> DROP TABLE IF EXISTS `QRTZ_TRIGGERS`;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

mysql> CREATE TABLE `QRTZ_TRIGGERS` (

`TRIGGER_NAME` varchar(80) NOT NULL,

`TRIGGER_GROUP` varchar(80) NOT NULL,

PRIMARY KEY (`TRIGGER_NAME`,`TRIGGER_GROUP`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.08 sec)

mysql>

mysql> DROP TABLE IF EXISTS `QRTZ_BLOB_TRIGGERS`;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

mysql> create table `QRTZ_BLOB_TRIGGERS` (`TRIGGER_NAME` varchar(80) not null, `TRIGGER_GROUP` varchar(80) not null, `BLOB_DATA` longblob, primary key (`TRIGGER_NAME`, `TRIGGER_GROUP`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.08 sec)

mysql>

mysql> alter table `QRTZ_BLOB_TRIGGERS` add index qrtz_blob_triggers_ibfk_1 (`TRIGGER_NAME`, `TRIGGER_GROUP`), add constraint qrtz_blob_triggers_ibfk_1 foreign key (`TRIGGER_NAME`, `TRIGGER_GROUP`) references `QRTZ_TRIGGERS` (`TRIGGER_NAME`, `TRIGGER_GROUP`) ON UPDATE RESTRICT ON DELETE RESTRICT;

Query OK, 0 rows affected (0.18 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>

mysql> show create table QRTZ_BLOB_TRIGGERS \G

*************************** 1. row ***************************

Table: QRTZ_BLOB_TRIGGERS

Create Table: CREATE TABLE `QRTZ_BLOB_TRIGGERS` (

`TRIGGER_NAME` varchar(80) NOT NULL,

`TRIGGER_GROUP` varchar(80) NOT NULL,

`BLOB_DATA` longblob,

PRIMARY KEY (`TRIGGER_NAME`,`TRIGGER_GROUP`),

KEY `qrtz_blob_triggers_ibfk_1` (`TRIGGER_NAME`,`TRIGGER_GROUP`),