MYSQL 5.5 删除主键 [英] MYSQL 5.5 Drop Primary Key

问题描述

我正在将我的quartz.net 版本从 1.0.3 升级到 2.0.2有一个数据库模式的迁移脚本，它是为 MSSQL 编写的，我正在尝试编写它的 MYSQL 版本.

I am upgrading my quartz.net version from 1.0.3 to 2.0.2 There is a migration script for database schema, which was was written for MSSQL, and I am trying to write a MYSQL version of it.

但是，我无法删除主键(我需要这样做).

However, I haven't been able to drop primary keys (which I need to).

脚本的原始 MSSQL 版本:

Original MSSQL version of script:

ALTER TABLE BLOB_TRIGGERS DROP CONSTRAINT BLOB_TRIGGERS_PKEY;
ALTER TABLE BLOB_TRIGGERS DROP CONSTRAINT BLOB_TRIGGERS_TRIGGER_NAME_FKEY;
ALTER TABLE SIMPLE_TRIGGERS DROP CONSTRAINT PK_SIMPLE_TRIGGERS;
ALTER TABLE SIMPLE_TRIGGERS DROP CONSTRAINT FK_SIMPLE_TRIGGERS_TRIGGERS;
ALTER TABLE CRON_TRIGGERS DROP CONSTRAINT PK_CRON_TRIGGERS;
ALTER TABLE CRON_TRIGGERS DROP CONSTRAINT FK_CRON_TRIGGERS_TRIGGERS;
ALTER TABLE TRIGGERS DROP CONSTRAINT PK_TRIGGERS;
ALTER TABLE TRIGGERS DROP CONSTRAINT FK_TRIGGERS_JOB_DETAILS;
ALTER TABLE JOB_DETAILS DROP CONSTRAINT PK_JOB_DETAILS;

为简单起见，我正在尝试第一个语句

For simplicity, I am trying the first statement there

ALTER TABLE BLOB_TRIGGERS DROP CONSTRAINT BLOB_TRIGGERS_PKEY;

以下是我的尝试和结果:

Here are what I have tried and results:

• ALTER TABLE BLOB_TRIGGERS DROP PRIMARY KEY;

[Err] 1025 - 将.\quartz_local#sql-df8_9"重命名为.\quartz_local\BLOB_TRIGGERS"时出错(错误号:150)

[Err] 1025 - Error on rename of '.\quartz_local#sql-df8_9' to '.\quartz_local\BLOB_TRIGGERS' (errno: 150)

• ALTER TABLE BLOB_TRIGGERS DROP INDEX 'PRIMARY';

[Err] 1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 1 行的PRIMARY"附近使用的正确语法

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''PRIMARY'' at line 1

• ALTER TABLE BLOB_TRIGGERS DROP INDEX PRIMARY;

[Err] 1025 - 将.\quartz_local#sql-df8_9"重命名为.\quartz_local\BLOB_TRIGGERS"时出错(错误号:150)

[Err] 1025 - Error on rename of '.\quartz_local#sql-df8_9' to '.\quartz_local\BLOB_TRIGGERS' (errno: 150)

• ALTER TABLE BLOB_TRIGGERS DROP PRIMARY KEY;
• ALTER TABLE BLOB_TRIGGERS DROP PRIMARY KEY;

[Err] 1025 - 将.\quartz_local#sql-df8_9"重命名为.\quartz_local\BLOB_TRIGGERS"时出错(错误号:150)

[Err] 1025 - Error on rename of '.\quartz_local#sql-df8_9' to '.\quartz_local\BLOB_TRIGGERS' (errno: 150)

我的Mysql版本是5.5.16

My Mysql version is 5.5.16

检查索引:

应要求提供外键:

推荐答案

(errno: 150) 是赠品:这意味着外键定义问题.我怀疑其他一些表有一个依赖于这个 PK 的外键约束，所以你需要先删除它，然后再重建它.

(errno: 150) is the giveaway: This means Foreign key definition problem. I suspect some other table has a foreign key constraint depending this PK, so you need to drop that first and rebuild it later.

随着您发布的图片，这变得更加清晰:

With the images you posted, this becomes clearer:

从 BLOBS_TRIGGERS 到 TRIGGERS 的 FK 是由 PK 组成的.因此，如果您放弃 PK，则约束将变得陈旧.您需要删除并稍后重新创建约束.

The FK from BLOBS_TRIGGERS to TRIGGERS is made up from the PK. So if you drop the PK, the contraint becomes stale. You need to drop and later recreate the constraint.

这篇关于MYSQL 5.5 删除主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！