MYSQL ENUM ; Invalid Syntax

Question

I am executing an SQL command that I have executed many other times in other environments but cannot a reason for this syntax error.

This is the code:

CREATE TABLE Customer
(
    customer_id INT IDENTITY (1,1) PRIMARY KEY,
    DOB DATETIME,
    Gender ENUM('M', 'F'),
    city_code (6) NOT NULL
)

I get an error:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'M'

If anybody has a pointer of something I am blatantly missing, please show the way. I'm using Microsoft MySql 2014 Express Edition

ENUM is not a function .. add also a space between ENUM and (..) — ScaisEdge
– ScaisEdge, Commented Nov 2, 2019 at 17:54
i changed the RDMS from MySQL to MSSQL tag as ENUM is a non SQL Server (MSSQL) datatype and the error is related to SQL Server (MSSQL) and not MySQL.. — Raymond Nijland
– Raymond Nijland, Commented Nov 2, 2019 at 18:14

GMB · Accepted Answer · 2019-11-02 18:27:03Z

When seeing Gender ENUM('M', 'F') and the error

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'M'.

I am pretty sure that you are mixing up RDMS SQL dialect syntax here.

As SQL Server does not know the ENUM datatype, this is a MySQL datatype.

This works in MySQL

CREATE TABLE Customer
(
    Gender ENUM('M', 'F')
); 

INSERT INTO Customer (Gender) VALUES('M');
INSERT INTO Customer (Gender) VALUES('F');


# : Error: WARN_DATA_TRUNCATED: Data truncated for column 'Gender' at row 1
#INSERT INTO Customer (Gender) VALUES('A');

see demo ..

One option to simulate/emulate this on SQL Server is with a CHECK constraint:

CREATE TABLE Customer
(
    Gender CHAR(1) CHECK(Gender IN ('M', 'F'))
); 

INSERT INTO Customer (Gender) VALUES('M');
INSERT INTO Customer (Gender) VALUES('F');

See demo

Using

INSERT INTO Customer (Gender) VALUES('A');

would cause an error

The INSERT statement conflicted with the CHECK constraint column 'Gender'

Note

Also city_code (6) NOT NULL is missing a datatype.

Wei Lin · Accepted Answer · 2019-11-02 17:54:21Z

0

city_code (6) it need to declare type.
IDENTITY(1,1) is other rdbms function not mysql,you can use AUTO_INCREMENT

Create Table Customer
(
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    DOB DATETIME,
    Gender ENUM('M', 'F'),
    city_code varchar(6) NOT NULL
)

✓

db<>fiddle here

answered Nov 2, 2019 at 17:54

Wei Lin

3,8213 gold badges27 silver badges61 bronze badges

2 Comments

Raymond Nijland Over a year ago

Msg 102, Level 15, State 1, Line 7 Incorrect syntax near 'M'. is a SQL Server (MSSQL) related error not a MySQL error..

Raymond Nijland Over a year ago

"yes,you're right. but his title is mysql." yes always check before answering if the tag and title is correct or makes sense with the rest of the question..

Collectives™ on Stack Overflow

MYSQL ENUM ; Invalid Syntax

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related