Mysql Drop Primary Key Error On Rename
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About mysql drop foreign key Us Learn more about Stack Overflow the company Business Learn more about hiring mysql drop foreign key if exists developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the
Error 1025 (hy000): Error On Rename Of
Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MYSQL 5.5 Drop Primary Key up
Can't Drop 'primary'; Check That Column/key Exists
vote 3 down vote favorite 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 mysql drop constraint 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: EDIT2: Foreign keys on request: mysql sql quartz.net alter share|improve this question edited Feb 11 '12 at 19:12 asked Feb 11 '12 at 18:49 Sadul
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss
Mysql Show Foreign Keys
the workings and policies of this site About Us Learn more about add foreign key mysql Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions cannot drop index needed in a foreign key constraint Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. http://stackoverflow.com/questions/9243016/mysql-5-5-drop-primary-key Join them; it only takes a minute: Sign up MySQL Error when dropping index (errno 150) up vote 8 down vote favorite 1 I've got problem with dropping foreign key index, I always get the same error ALTER TABLE `comments` DROP INDEX `id_user` which outputs 1025 - Error on rename of './postuj_cz1/#sql-d834_a0c704' to './postuj_cz1/comments' (errno: 150) The id_user http://stackoverflow.com/questions/1451042/mysql-error-when-dropping-index-errno-150 on the other table is simple primary key index. I'm using MySQL version 5.0.85 mysql sql mysql-error-1025 share|improve this question edited Apr 30 '11 at 17:30 OMG Ponies 199k37360417 asked Sep 20 '09 at 13:43 Jakub Arnold 32.3k62180262 1 Try dropping all foreign keys to the table first. –David Andres Sep 20 '09 at 13:47 Actually I don't have any fk to the table –Jakub Arnold Sep 20 '09 at 13:58 add a comment| 4 Answers 4 active oldest votes up vote 2 down vote accepted According to this link, the error relates to the definition of the primary key field. The error isn't about the foreign key index. Check the primary key for the COMMENTS table to make sure it does not have the UNSIGNED keyword while the COMMENTS.id_user foreign key had the UNSIGNED keyword. This keyword was causing the problem - inconsistent type of field. To fix, add the UNSIGNED keyword to the primary key definition for the COMMENTS table. Or remove the UNSIGNED keyword from the foreign
Email Updates: Status: Verified Impact on me: None Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request) Version:5.0.23-debug OS:Linux (SUSE 10.0 / 64-bit) https://bugs.mysql.com/bug.php?id=20525 Assigned to: Assigned Account Triage: Triaged: D5 (Feature request) View Add Comment Files Developer Edit Submission View Progress Log Contributions [18 Jun 2006 20:43] Peter Gulutzan Description: If a table http://symfony-world.blogspot.com/2010/10/mysql-error-on-rename-when-dropping.html t has a foreign key, I can't say ALTER TABLE t DROP PRIMARY KEY How to repeat: mysql> create table t1 (s1 int, primary key (s1)) engine=innodb; Query OK, 0 foreign key rows affected (0.08 sec) mysql> create table t2 (s1 int, primary key (s1), foreign key (s1) references t1 (s1)) engine=innodb; Query OK, 0 rows affected (0.01 sec) mysql> alter table t2 drop primary key; ERROR 1025 (HY000): Error on rename of './db9/#sql-4099_1' to './db9/t2' (errno: 150) [18 Jun 2006 21:37] Hartmut Holzgraefe Dropping the primary key leads to a violation of error on rename the "In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order" condition, but unlike on a CREATE TABLE the "Such an index is created on the referencing table automatically if it does not exist" behavior is not triggered (quoted from http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html ) Not sure whether this is really a but, i.e. whether DROP KEY should trigger the creation of an implicit key here ... setting this to "verified" for now, to have higher forces than me decide on this [13 Dec 2007 13:17] Heikki Tuuri Konstantin's foreign key implementation may solve this in the future. Legal Policies Your Privacy Rights Terms of Use Contact Us Portions of this website are copyright © 2001, 2002 The PHP Group Page generated in 0.022 sec. using MySQL 5.7.12-enterprise-commercial-advanced-log Content reproduced on this site is the property of the respective copyright holders.It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party. © 2016, Oracle Corporation and/or its affiliates
Recently I wanted to remove a column from phpmyadmin that is covered with a constraint. Impossible to do so, MySQL throws following error: ERROR 1025 (HY000): Error on rename of './optiner_prestadmin/#sql-6bc_3e6' to './optiner_prestadmin/order_history' (errno: 150) You may get above error when executing a query removing index, e.g. ALTER TABLE order_history DROP INDEX user_id;Fortunately, there is a solution, not very easy to find in the internet (I used comments from this article). execute the following in MySQL: SHOW CREATE TABLE order_history;You shall get something like: order_history | CREATE TABLE `order_history` ( `order_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `status_id` bigint(20) NOT NULL, `informed` tinyint(1) DEFAULT '0', `comment` text, `created_at` datetime NOT NULL, `created_by` bigint(20) NOT NULL DEFAULT '1', PRIMARY KEY (`id`), KEY `order_id_idx` (`order_id`), KEY `user_id_idx` (`user_id`), KEY `status_id_idx` (`status_id`), KEY `created_by_idx` (`created_by`), CONSTRAINT `order_history_created_by_sf_guard_user_id` FOREIGN KEY (`created_by`) REFERENCES `sf_guard_user` (`id`) ON DELETE CASCADE, CONSTRAINT `order_history_order_id_order_info_id` FOREIGN KEY (`order_id`) REFERENCES `order_info` (`id`), CONSTRAINT `order_history_status_id_order_status_id` FOREIGN KEY (`status_id`) REFERENCES `order_status` (`id`), CONSTRAINT `order_history_user_id_sf_guard_user_id` FOREIGN KEY (`user_id`) REFERENCES `sf_guard_user` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=28305 DEFAULT CHARSET=utf8 we want to delete the whole column user_id (with all corresponding indices, constraints, etc.) - need to drop the FOREIGN KEY first: ALTER TABLE order_history DROP FOREIGN KEY `order_history_user_id_sf_guard_user_id`;you'll find the name of the foreign key in the last constraint clause. finally, drop the column: ALTER TABLE `order_history` DROP `user_id`;and that's it! Query OK, 28