SadullahCeran SadullahCeran - 1 year ago 235
SQL Question

MYSQL 5.5 Drop Primary Key

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

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download