CODEWITHSUNDEEP
mysql
MOHD MUQEEM NASIR
MOHD MUQEEM NASIR

Reputation: 19

MYSQL ENUM ; Invalid Syntax

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

Upvotes: 0

Views: 1986

Answers (2)

Raymond Nijland
Raymond Nijland

Reputation: 11602

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.

Upvotes: 2

Wei Lin
Wei Lin

Reputation: 3811

  1. city_code (6) it need to declare type.

  2. 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

Upvotes: 0

Related Questions