MYSQL 5.5 Drop Primary Key

Question

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

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 - 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 - 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 - Error on rename of '.\quartz_local#sql-df8_9' to '.\quartz_local\BLOB_TRIGGERS' (errno: 150)

ALTER TABLE BLOB_TRIGGERS DROP PRIMARY KEY;

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

My Mysql version is 5.5.16

EDIT: To check the indexes: MYSQL Show index results

EDIT2: Foreign keys on request: Create table sql

`ALTER TABLE BLOB_TRIGGERS DROP PRIMARY KEY;` is syntactically the correct version, so you need to look deepr — Eugen Rieck, Feb 11 '12 at 18:53
AFAIK, it is. But no luck finding the problem and the solution. What should I be checking? — SadullahCeran, Feb 11 '12 at 18:57

Eugen Rieck · Accepted Answer · 2012-02-11T19:24:35.137

4

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

Edit: With the images you posted, this becomes clearer:

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.

edited Feb 11 '12 at 19:24

answered Feb 11 '12 at 19:00

Eugen Rieck

64,175
10
70
92

I confirm there is a foreign key defined in this table pointing to another table. – SadullahCeran Feb 11 '12 at 19:13
I suspect You need to look the other way round: FKs on other tables referencing this table – Eugen Rieck Feb 11 '12 at 19:15
I checked it. There is no foreign key constraint pointing to BLOB_CALENDARS table. – SadullahCeran Feb 11 '12 at 19:17
Wasn't his about BLOB_TRIGGERS ? – Eugen Rieck Feb 11 '12 at 19:19
Sorry for typo. BLOB_TRIGGERS, indeed. – SadullahCeran Feb 11 '12 at 19:20
However, if I first remove the foreign key pointing from BLOB_TRIGGERS to other tables, then I can remove primary key. Problem is solved, but I don't really get the reason behind. – SadullahCeran Feb 11 '12 at 19:21
1

You can't have a `FOREIGN KEY` to another table without an index. Your Foreign Key constraint was using the (Primary Key) index. – ypercubeᵀᴹ Feb 11 '12 at 19:24
Now I get it. Thanks for help. – SadullahCeran Feb 11 '12 at 20:00

score 1 · Answer 2 · answered Feb 11 '12 at 19:07

1

After brief Googling, I'm pretty sure the error message is a little misleading. There seem to be a lot of ALTER TABLE statements that might result in that error message.

I'd check to see if there are foreign key references to this table.

answered Feb 11 '12 at 19:07

Mike Sherrill 'Cat Recall'

91,602
17
122
185

How can I check foreign key references pointing to this table (BLOB_TRIGGERS) ? – SadullahCeran Feb 11 '12 at 19:14
I think you need to [query the KEY_COLUMN_USAGE table](http://dev.mysql.com/doc/refman/5.5/en/key-column-usage-table.html) in information schema views. – Mike Sherrill 'Cat Recall' Feb 11 '12 at 19:17
I did and confirmed there is no foreign key pointing to BLOB_TRIGGERS. – SadullahCeran Feb 11 '12 at 19:20
However, if I first remove the foreign key pointing from BLOB_TRIGGERS to other tables, then I can remove primary key. Problem is solved, but I don't really get the reason behind. – SadullahCeran Feb 11 '12 at 19:21
It's not unusual for any SQL dbms to complain when you try to drop something that's involved in a dependency. MySQL just seems to have a suboptimal error message for it. When you try to drop a primary key constraint that's referenced by a foreign key in PostgreSQL, you get an error message that basically says a foreign key depends on the constraint you tried to drop, so use DROP . . . CASCADE to drop dependent objects. – Mike Sherrill 'Cat Recall' Feb 11 '12 at 19:27

score 0 · Answer 3 · answered Apr 26 '16 at 03:34

0

I had the same problem. Deleting foreign keys in the table did not help. There were no other tables referencing the one that had the primary key I was trying to drop. I finally solved the problem by using mysqldump to export the table to an ASCII file. I then edited the file to change the primary key to one I wanted, then I reimported using the mysql command line interface.

answered Apr 26 '16 at 03:34

Carl Grundstrom

1
1

2

This should be comment – ketan Apr 26 '16 at 03:52

score 0 · Answer 4 · answered Jul 19 '16 at 10:07

0

ALTER TABLE tablename DROP PRIMARY KEY;

answered Jul 19 '16 at 10:07

Sanjeev Kumar

193
1
7

MYSQL 5.5 Drop Primary Key

4 Answers4

Linked