How can I avoid getting this MySQL error Incorrect column specifier for column COLUMN NAME

Question

How can I avoid getting this MySQL error Incorrect column specifier for column topic id    MySQL Error        1063 - Incorrect column specifier for column  topic id    SQL Schema     CREATE TABLE discussion topics       topic id char 36  NOT NULL AUTO INCREMENT      project id char 36  NOT NULL      topic subject VARCHAR 255  NOT NULL      topic content TEXT default NULL      date created DATETIME NOT NULL      date last post DATETIME NOT NULL      created by user id char 36  NOT NULL      last post user id char 36  NOT NULL      posts count char 36  default NULL      PRIMARY KEY  topic id    ENGINE InnoDB DEFAULT CHARSET utf8 AUTO INCREMENT 1

User · Answer

Quoting the doc      Some attributes do not apply to all data types  AUTO INCREMENT applies   only to integer and floating-point types  DEFAULT does not apply to   the BLOB or TEXT types    In your case  you re trying to apply AUTO INCREMENT modifier to char column  To solve this  either drop AUTO INCREMENT altogether  that means you ll have to generate a unique id on the application level  or just change topic id type to the relevant integer one   As a sidenote  it makes little sense using char 36  to store the posts count  so that column s type probably has to be changed as well  It looks like you re going this way to prevent integer overflow - but if you re dealing with more than 18446744073709551615 posts  the biggest number that can be stored in BIGINT UNSIGNED column  in a single topic  you have far bigger problem on your side probably

User · Answer

You cannot auto increment the char values  It should be int or long integers or floating points   Try with this   CREATE TABLE discussion topics       topic id int 5  NOT NULL AUTO INCREMENT      project id char 36  NOT NULL      topic subject VARCHAR 255  NOT NULL      topic content TEXT default NULL      date created DATETIME NOT NULL      date last post DATETIME NOT NULL      created by user id char 36  NOT NULL      last post user id char 36  NOT NULL      posts count char 36  default NULL      PRIMARY KEY   topic id     ENGINE InnoDB DEFAULT CHARSET utf8 AUTO INCREMENT 1    Hope this helps

User · Answer

The auto increment property only works for numeric columns  integer and floating point   not char columns   CREATE TABLE discussion topics       topic id INT NOT NULL AUTO INCREMENT      project id char 36  NOT NULL      topic subject VARCHAR 255  NOT NULL      topic content TEXT default NULL      date created DATETIME NOT NULL      date last post DATETIME NOT NULL      created by user id char 36  NOT NULL      last post user id char 36  NOT NULL      posts count char 36  default NULL      PRIMARY KEY  topic id    ENGINE InnoDB DEFAULT CHARSET utf8 AUTO INCREMENT 1

User · Answer

To use AUTO INCREMENT you need to deifne column as INT or floating-point types  not CHAR   AUTO INCREMENT use only unsigned value  so it s good to use UNSIGNED as well   CREATE TABLE discussion topics         topic id INT NOT NULL unsigned AUTO INCREMENT       project id char 36  NOT NULL       topic subject VARCHAR 255  NOT NULL       topic content TEXT default NULL       date created DATETIME NOT NULL       date last post DATETIME NOT NULL       created by user id char 36  NOT NULL       last post user id char 36  NOT NULL       posts count char 36  default NULL       PRIMARY KEY  topic id     ENGINE InnoDB DEFAULT CHARSET utf8 AUTO INCREMENT 1

User · Answer

I was having the same problem  but using Long type  I changed for INT and it worked for me   CREATE TABLE lists    id INT NOT NULL AUTO INCREMENT   desc varchar 30    owner varchar 20    visibility boolean   PRIMARY KEY  id

[mysql] How can I avoid getting this MySQL error Incorrect column specifier for column COLUMN NAME?

Examples related to mysql

Examples related to sql

Examples related to auto-increment

Examples related to ddl