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; 

Answer 1

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;