1025 ошибка при переименовании

I tried this in mysql:

mysql> alter table region drop column country_id;

And got this:

ERROR 1025 (HY000): Error on rename of './product/#sql-14ae_81' to
'./product/region' (errno: 150)

Any ideas? Foreign key stuff?

I had a similar issues once. I deleted the primary key from TABLE A but when I was trying to delete the foreign key column from table B I was shown the above same error.

You can’t drop the foreign key using the column name and to bypass this in PHPMyAdmin or with MySQL, first remove the foreign key constraint before renaming or deleting the attribute.

answered Jan 12, 2016 at 5:40

Take a look in error file for your mysql database. According to Bug #26305 my sql do not give you the cause. This bug exists since MySQL 4.1

If you are using a client like MySQL Workbench, right click the desired table from where a foreign key is to be deleted, then select the foreign key tab and delete the indexes.

Then you can run the query like this:

alter table table_name drop foreign_key_col_name;

answered Jan 8, 2013 at 20:25

There is probably another table with a foreign key referencing the primary key you are trying to change.

To find out which table caused the error you can run SHOW ENGINE INNODB STATUS and then look at the LATEST FOREIGN KEY ERROR section

Use SHOW CREATE TABLE categories to show the name of constraint.

Most probably it will be categories_ibfk_1

Use the name to drop the foreign key first and the column then:

ALTER TABLE categories DROP FOREIGN KEY categories_ibfk_1;
ALTER TABLE categories DROP COLUMN assets_id;

answered Jan 11, 2016 at 13:24

I got this error with MySQL 5.6 but it had nothing to do with Foreign keys. This was on a Windows 7 Professional machine acting as a server on a small LAN.

The client application was doing a batch operation that creates a table fills it with some external data then runs a query joining with permanent tables then dropping the «temporary» table. This batch does this approximately 300 times and this particular routine had been running week in week out for several years when suddenly we get the Error 1025 Unable to rename problem at a random point in the batch.

In my case the application was using 4 DDL statements a CREATE TABLE followed by 3 CREATE INDEX, there is no foreign key. However only 2 of the indexes actually get created and the actual table .frm file was renamed, at the point of failure.

My solution was to get rid of the separate CREATE INDEX statements and create them using the CREATE TABLE statement. This at the time of writing has solved the issue for me and my help someone else scratching their head when they find this thread.

answered Jan 4, 2019 at 21:27

In my case, I was using MySQL workbench and I faced the same issue while dropping one of my columns in a table. I could not find the name of the foreign key. I followed the following steps to resolve the issue:

1. Rt. click on your schema and select ‘schema inspector’. This gives you various tables, columns, indexes, ect.

2. Go to the tab named ‘Indexes’ and search the name of the column under the column named ‘Column’. Once found check the name of the table for this record under the column name ‘Table’. If it matches the name of the table you want, then note down the name of the foreign key from the column named ‘Name’.

3. Now execute the query : ALTER table tableNamexx DROP KEY foreignKeyName;

4. Now you can execute the drop statement which shall execute successfully.

answered Feb 25, 2016 at 7:51

Doing

SET FOREIGN_KEY_CHECKS=0;

before the Operation can also do the trick.

answered Jan 14, 2017 at 16:25

So I am trying to add a primary key to one of the tables in my database. Right now it has a primary key like this:

PRIMARY KEY (user_id, round_number)

Where user_id is a foreign key.

I am trying to change it to this:

PRIMARY KEY (user_id, round_number, created_at)

I am doing this in phpmyadmin by clicking on the primary key icon in the table structure view.

This is the error I get:

#1025 - Error on rename of './database/#sql-2e0f_1254ba7' to './database/table' (errno: 150)

It is a MySQL database with InnoDB table engine.

answered Jun 20, 2013 at 13:03

If you are trying to delete a column which is a FOREIGN KEY, you must find the correct name which is not the column name. Eg: If I am trying to delete the server field in the Alarms table which is a foreign key to the servers table.

1. SHOW CREATE TABLE alarm;
   Look for the CONSTRAINT `server_id_refs_id_34554433` FORIEGN KEY (`server_id`) REFERENCES `server` (`id`) line.
2. ALTER TABLE `alarm` DROP FOREIGN KEY `server_id_refs_id_34554433`;
3. ALTER TABLE `alarm` DROP `server_id`

This will delete the foreign key server from the Alarms table.

I had this problem, it is for foreign-key

Click on the Relation View (like the image below) then find name of the field you are going to remove it, and under the Foreign key constraint (INNODB) column, just put the select to nothing! Means no foreign-key

Hope that works!

answered Feb 29, 2016 at 6:37

If you are adding a foreign key and faced this error, it could be the value in the child table is not present in the parent table.

Let’s say for the column to which the foreign key has to be added has all values set to 0 and the value is not available in the table you are referencing it.

You can set some value which is present in the parent table and then adding foreign key worked for me.

answered Nov 18, 2016 at 7:32

create table t1 (a int, b int, index(a)) engine=innodb;

create table t2 (a int, foreign key (a) references t1(a), foreign key (a) references t1(b)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)

SHOW ENGINE INNODB STATUS;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-16 06:37:39 0x14c1c Error in foreign key constraint of table test/t2:
foreign key (a) references t1(b)) engine=innodb:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constr
aints.html for correct foreign key definition.
————

— при использовании оператора ALTER ошибка и секция
— LATEST FOREIGN KEY ERROR будут содержать внутреннее имя таблицы test.#sql-a64_1

create table t2 (a int) engine=innodb;
alter table t2 add foreign key (a) references t1(a), add foreign key (a) references t1(b);
ERROR 1005 (HY000): Cannot create table ‘test.#sql-a64_1’ (errno: 150)

— в новых версиях парсер MySQL определяет некорректность
— конструкции и возвращает другую ошибку (без errno 150)

alter table t2 add foreign key (a) references t1(a), add foreign key (a) references t1(b);
ERROR 1215 (HY000): Cannot add foreign key constraint

— аналогично и для оператора CREATE

drop table t2;
create table t2 (a int, foreign key (a) references t1(a), foreign key (a) references t1(b)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint

create table t1 (a int, b int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;

alter table t1 drop a;
ERROR 1025 (HY000): Error on rename of ‘.test#sql-d6c_5′ to ‘.testt1′ (errno: 150)

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161220  7:14:25 Error in foreign key constraint of table test/t2:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
  CONSTRAINT «t2_ibfk_1» FOREIGN KEY («a») REFERENCES «t1» («a»)
The index in the foreign key in table is «a»
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
InnoDB: Renaming table `test`.`#sql-d6c_5` to `test`.`t1` failed!
———

create table t1(id int not null primary key, b int, key(b)) engine=innodb;
Query OK, 0 rows affected (0.22 sec)

alter table t1 add foreign key(id,b) references t1(id);
ERROR 1005 (HY000): Can‘t create table ‘test.#sql-d88_b’ (errno: 150)

show warnings;
+——-+——+—————————————————+
| Level | Code | Message                                           |
+——-+——+—————————————————+
| Error | 1005 | Can‘t create table ‘test.#sql-d88_b’ (errno: 150) |
+——-+——+—————————————————+

— понять, что причина в синтаксической ошибке
— можно только из:

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
160605 22:28:23 Error in foreign key constraint of table test/#sql-d88_b:
foreign key(id,b) references t1(id):
Syntax error close to:

— в новых версиях парсер распознает синтаксическую ошибку
— и сообщает об этом:
ERROR 1239 (42000): Incorrect foreign key definition for ‘foreign key without name’: Key reference and table reference don‘t match

create table t1 (a text , index(a(50))) engine=innodb;

create table t2 (a text, foreign key (a) references t1(a)) engine=innodb;
ERROR 1170 (42000): BLOB/TEXT column ‘a’ used in key specification without a key length

— MySQL автоматически пытается создать индекс на колонке `a`, и
— сообщает, что нельзя создать индекс по всей длине поля типа text.
— Хорошо, укажем префикс и получим errno 150:

create table t2 (a text, foreign key (a(50)) references t1(a)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)

— понять, что произошла ошибка синтаксиса можно:
— или через show engine innodb status;
— или внимательно сравнить разрешенный синтаксис в документации
— с написанной командой.

— например, если у одной колонки мы определим
— атрибут unsigned, а у другой нет, то:
create table t1 (a int unsigned, index(a)) engine=innodb;

create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint

— в старых версиях будет ошибка вида
ERROR 1005 (HY000): Cannott create table ‘test.t2’ (errno: 150)

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-26 03:00:47 0x10894 Error in foreign key constraint of table world/t2:
foreign key (a) references t1(a)) engine=innodb:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constr
aints.html for correct foreign key definition.
————

create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;

MariaDB [test]> alter table t1 modify a int unsigned;
ERROR 1025 (HY000): Error on rename of ‘.test#sql-d6c_6′ to ‘.testt1′ (errno: 150)

create table t1 (a int not null, index(a)) engine=innodb;

create table t2 (a int not null, foreign key (a) references t1(a) on delete set null) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint

— в старых версиях будет:
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-26 06:24:42 0x10894 Error in foreign key constraint of table world/t2:
foreign key (a) references t1(a) on delete set null) engine=innodb:
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL.
————

create table t1 (a int not null, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a) on delete set null) engine=innodb;

alter table t2 modify a int not null;
ERROR 1025 (HY000): Error on rename of ‘.test#sql-d6c_6′ to ‘.testt2′ (errno: 150)

create table t1 (a int, index(a)) partition by range (a)  
(partition p0 values less than (10),
partition p1 values less than (20),
partition p2 values less than maxvalue);

create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161223 19:38:14 Error in foreign key constraint of table test/t2:
foreign key (a) references t1(a)) engine=innodb:
Cannot resolve table name close to:
(a)) engine=innodb
———
— сообщение указывает на то, что родительская таблица
— не найдена в словаре данных innodb (bug: 84331)

— в новых версиях ошибка будет иметь вид:

create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint

create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;

alter table t1 PARTITION BY HASH(a) PARTITIONS 8;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

show engine innodb status;
— не содержит секцию LATEST FOREIGN KEY ERROR