Adding new enum column to an existing table

Question

I'm trying to add a gender column to my table with this query:

ALTER TABLE QRCodeUser ADD gender CHAR(1) enum('M','F') NOT NULL;

I get this error:

#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 'enum('M','F') NOT NULL' at line 1

What's my mistake?

`CHAR(1)` is a type. `enum('M','F')` is also a type. A column can only have one type. — Mark Amery, May 09 '15 at 13:44

score 41 · Accepted Answer · answered May 09 '15 at 12:23

41

Try this (you dont need to specify the size, char(1) ) :

ALTER TABLE QRCodeUser ADD gender  enum('M','F') NOT NULL;

answered May 09 '15 at 12:23

dsharew

10,377
6
49
75

Thanks so much, it works ! But why did it not work my solution....I dont understand... – max85 May 09 '15 at 12:28
@dsharew what is 'M' and 'F' stand for? – Fai Zal Dong Sep 15 '17 at 04:22
It is part of the question; It seems for "Male" and "Female" though – dsharew Sep 15 '17 at 07:58
1

I think this enum list in the answer may need to be updated for 2019 :laugh: – Andrew Sohn Feb 26 '20 at 16:37
@AndrewSohn going to need to convert it to a varchar :P – Elias Jul 15 '21 at 11:43

score 6 · Answer 2 · edited Nov 26 '18 at 11:51

6

Correct usage of syntax:

ALTER TABLE table_name ADD column_name  enum(`field1`,`field2`,...);

edited Nov 26 '18 at 11:51

Ankush Madankar

3,689
4
40
74

answered Nov 26 '18 at 10:49

Krishnamurthy Kulkarni

59
1
1

enum defines a set of values to be assigned to the column a better example is ALTER TABLE table_name ADD column_name enum(`value1`,`value2`,...,value_n); can't define also CHAR(1) because ENUM os a type mysql internally use int to represents ENUM it uses the ordinal positions of the value in the definition so value1 is eq to 0 value2 is eq to 1 and value_n is eq to n HTH – TexWiller May 09 '19 at 10:11

Adding new enum column to an existing table

2 Answers2