Error renaming a column in MySQL

Question

How do I rename a column in table xyz? The columns are:

Manufacurerid, name, status, AI, PK, int

I want to rename to manufacturerid

I tried using PHPMyAdmin panel, but I get this error:

MySQL said: Documentation
#1025 - Error on rename of '.\shopping\#sql-c98_26' to '.\shopping\tblmanufacturer' (errno: 150)

You're trying to change a table that has a foreign key reference in it. — mellowsoon, Oct 23 '10 at 03:41
possible duplicate of [Renaming foreign-key columns in MySQL](http://stackoverflow.com/questions/2014498/renaming-foreign-key-columns-in-mysql) — OMG Ponies, Oct 23 '10 at 03:44

score 753 · Accepted Answer · edited Nov 03 '16 at 21:53

753

Lone Ranger is very close... in fact, you also need to specify the datatype of the renamed column. For example:

ALTER TABLE `xyz` CHANGE `manufacurerid` `manufacturerid` INT;

Remember :

Replace INT with whatever your column data type is (REQUIRED)
Tilde/ Backtick (`) is optional

edited Nov 03 '16 at 21:53

c0degeas

762
9
19

answered Oct 23 '10 at 04:08

Matt Diamond

11,646
5
29
36

12

I think the column contains a foreign key to getting the error of the answer so you have to Drop foreign key, alter table, and add foreign key (it's better to backup first) and u can alter it by only changing the name in right click on the table - alter table – Chris Sim May 09 '14 at 10:06
7

Keep in mind that in this solution you lose all other column definitions such as nullability, default value, etc. (see: http://stackoverflow.com/questions/8553130/rename-a-column-in-mysql-table-without-having-to-repeat-its-type-definition). – Dejan Jul 13 '15 at 10:50
So basically, instead of `MODIFY column ` (for redefining a column), it's `CHANGE column new_column `? – mwfearnley Oct 13 '15 at 15:54
`SHOW CREATE TABLE table_name` can help you figure out current column definition – Mr. Deathless Jan 21 '16 at 18:32
@Dejan thanks and this is really annoying, why require a type when you can't specify any other modifiers? Either require a type + modifiers or don't require it at all. – JMac Apr 12 '16 at 05:12
@JMac It appears that at lest since MySQL 5.1 all modifiers are supported, the type is just the only one that is required, and it is always required (like when you create a table). – J.D. Dec 21 '16 at 19:43

score 44 · Answer 2 · edited Jul 15 '21 at 22:39

44

The standard MySQL rename statement is:

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name 
CHANGE [COLUMN] old_col_name new_col_name column_definition 
[FIRST|AFTER col_name]

For this example:

ALTER TABLE xyz CHANGE manufacurerid manufacturerid datatype(length)

Reference: MYSQL 5.1 ALTER TABLE Syntax

edited Jul 15 '21 at 22:39

informatik01

16,038
10
74
104

answered Mar 31 '14 at 02:58

dongpf

1,537
16
8

score 42 · Answer 3 · edited May 20 '16 at 10:51

42

FOR MYSQL:

ALTER TABLE `table_name` CHANGE `old_name` `new_name` VARCHAR(255) NOT NULL;

FOR ORACLE:

ALTER TABLE `table_name` RENAME COLUMN `old_name` TO `new_name`;

edited May 20 '16 at 10:51

T.Todua

53,146
19
236
237

answered Nov 12 '14 at 10:35

mahbub_siddique

1,755
18
22

3

It doesn't work if you use `"` for the column names for MySQL. Use nothing or ` instead. – Alexis N-o Jul 06 '15 at 17:37

score 14 · Answer 4 · edited Jun 20 '20 at 09:12

14

EDIT

You can rename fields using:

ALTER TABLE xyz CHANGE manufacurerid manufacturerid INT

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 23 '10 at 03:37

Lone Ranger

296
1
3

1

#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 '' at line 1 – Bharanikumar Oct 23 '10 at 04:02
17

This would be useful, but it's not true to the documentation you cited: "When you use CHANGE or MODIFY, **column_definition must include the data type and all attributes that should apply to the new column** [...] Attributes present in the original definition but not specified for the new definition are not carried forward. " – artfulrobot Jan 19 '13 at 23:34
it has to be "ALTER TABLE xyz CHANGE manufacurerid manufacturerid datatype(length)" – Chris Sim May 09 '14 at 08:30

score 7 · Answer 5 · edited Nov 17 '14 at 17:58

7

There is a syntax problem, because the right syntax to alter command is ALTER TABLE tablename CHANGE OldColumnName NewColunmName DATATYPE;

edited Nov 17 '14 at 17:58

Torsten

1,696
2
21
42

answered Aug 26 '14 at 09:22

Darshan

2,094
1
15
15

score 2 · Answer 6 · edited Oct 22 '14 at 09:06

2

With MySQL 5.x you can use:

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name DATATYPE NULL DEFAULT NULL;

edited Oct 22 '14 at 09:06

Morix Dev

2,700
1
28
49

answered Oct 22 '14 at 08:48

MIKE KIM

29
1

Hasib Kamal Chowdhury · Answer 7 · 2018-09-20T10:26:36.730

1

Renaming a column in MySQL :

ALTER TABLE mytable CHANGE current_column_name new_column_name DATATYPE;

edited Sep 20 '18 at 10:26

answered May 06 '17 at 17:08

Hasib Kamal Chowdhury

2,476
26
28

score 0 · Answer 8 · answered Mar 16 '19 at 19:32

0

ALTER TABLE CHANGE ;

Example:

ALTER TABLE global_user CHANGE deviceToken deviceId VARCHAR(255) ;

answered Mar 16 '19 at 19:32

vaquar khan

10,864
5
72
96

score -5 · Answer 9 · answered Jan 29 '15 at 06:35

-5

SYNTAX

alter table table_name rename column old column name to new column name;

Example:

alter table library rename column cost to price;

answered Jan 29 '15 at 06:35

Abinaya

15
1

2

In mysql RENAME is used for renaming table not a column ,to rename a column use CHANGE. – jathin May 27 '15 at 08:14
Question is for MySQL - this isn't valid syntax in MySQL. – tjbp Apr 14 '16 at 16:33
As others note, please use valid MySQL syntax. – fool4jesus Mar 22 '17 at 18:55
There is invalid mysql syntax. – Hasib Kamal Chowdhury Apr 01 '18 at 08:18

Error renaming a column in MySQL

9 Answers9

EDIT

Linked

Related