MODIFY COLUMN error in MySQL

Question

I have this sql script that creates a table in my MySql database.

CREATE TABLE USER_ACCOUNT
(ID             DECIMAL(10)     NOT NULL,
 USERNAME       VARCHAR (50)    NOT NULL,
 PASSWORD       VARCHAR (25)    NOT NULL,
 EMAIL          VARCHAR (50)    NOT NULL,
 FIRST_NAME     VARCHAR (25)    NULL DEFAULT NULL,
 MIDDLE_NAME    VARCHAR (25)    NULL DEFAULT NULL,
 LAST_NAME      VARCHAR (25)    NULL DEFAULT NULL,
 CREATE_DATE    TIMESTAMP       NULL DEFAULT CURRENT_TIMESTAMP);

ALTER TABLE USER_ACCOUNT
  ADD CONSTRAINT USER_ACCOUNT_PK_ID
      PRIMARY KEY (ID);

ALTER TABLE USER_ACCOUNT
  ADD CONSTRAINT USER_ACCOUNT_UK_USERNAME
      UNIQUE (USERNAME);

ALTER TABLE USER_ACCOUNT 
  MODIFY COLUMN ID DECIMAL(10) NOT NULL AUTO_INCREMENT;

I understand that AUTO_INCREMENT has to be added to a KEY. I am trying to add AUTO_INCREMENT to the primary key ID but the line

ALTER TABLE USER_ACCOUNT 
  MODIFY COLUMN ID DECIMAL(10) NOT NULL AUTO_INCREMENT;

Has the following syntax error:

Syntax error: unexpected 'DECIMAL' (decimal)

And then has this error when the script is run:

Error Code: 1063. Incorrect column specifier for column 'ID'

What's going on with my statement? I've looked all over online and it looks like the correct syntax.

Most probably the error has to do with DECIMAL data type. I don't think you can apply AUTO_INCREMENT to a column of this type. Try BIGINT instead. — Giorgos Betsos
– Giorgos Betsos, Commented May 24, 2015 at 20:36
@GiorgosBetsos Tried it and I get the same error with BIGINT — Graham
– Graham, Commented May 24, 2015 at 20:40
@GiorgosBetsos Actually, my mistake, it still shows the syntax error in the MySQL Workbench but when I run the script there are no errors and everything works fine. So that was the answer. Thank you. — Graham
– Graham, Commented May 24, 2015 at 20:51
@Graham You should accept a correct answer, so other people can find the solution. — Scoutman
– Scoutman, Commented May 24, 2015 at 21:03
@Scoutman I see you changed your answer to what GiorgosBetsos said.... fish much? — Graham
– Graham, Commented May 24, 2015 at 23:34

Scoutman · Accepted Answer · 2015-05-24 21:13:36Z

1

Use for the ID integer datatype.

ALTER TABLE `user_account` CHANGE `ID` `ID` BIGINT  NOT NULL ;
ALTER TABLE `user_account` CHANGE `ID` `ID` BIGINT NOT NULL AUTO_INCREMENT ;

For auto increment you have to use a integer data type: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT

A typical data type for a index is INT or BIGINT.

https://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

https://dev.mysql.com/doc/refman/5.0/en/integer-types.html

edited May 24, 2015 at 21:13

answered May 24, 2015 at 20:35

Scoutman

1,63012 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zaffar Saffee · Accepted Answer · 2015-05-24 20:33:14Z

0

If possible with out data loss, DROP this column and add new columns with your required attributes,

to my experience, altering PK column always teases :(

answered May 24, 2015 at 20:33

Zaffar Saffee

6,3056 gold badges42 silver badges78 bronze badges

Comments

Graham · Accepted Answer · 2015-05-24 20:53:16Z

0

As what Giorgos Betsos mentioned in the comments above, I changed DECIMAL(10) to BIGINT and it worked fine.

answered May 24, 2015 at 20:53

Graham

5,91213 gold badges65 silver badges96 bronze badges

Collectives™ on Stack Overflow

MODIFY COLUMN error in MySQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related