Incorrect syntax near 'a' with enum('a','b','c')

Question

CREATE TABLE ec_recurring 
(   
    [recurring id] int NOT NULL IDENTITY,   
    [price] decimal(10,4) NOT NULL,
    [frequency] enum('day','week','semi_month','month','year') NOT NULL,   
    [duration] cast(10 as int) unsigned NOT NULL,
    [cycle] cast(10 as int) unsigned NOT NULL,
    [trial status] tinyint(4) NOT NULL,
    [trial price] decimal(10,4) NOT NULL,
    [trial frequency] enum('day','week','semi_month','month','year') NOT NULL,   
    [trial duration] cast(10 as int) unsigned NOT NULL,
    [trial cycle] cast(10 as int) unsigned NOT NULL,
    [status] tinyint(4) NOT NULL,   
    [sort order] cast(11 as int) NOT NULL,
    [date created] datetime2 NOT NULL,   
    [date modified] datetime2 NOT NULL,
    [created by] varchar(20) NOT NULL,   
    [modified by] varchar(20) NOT NULL,
    [active] tinyint(4) NOT NULL,   

    PRIMARY KEY ([recurring id])
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

This was my code. Why does this error happen near day and cast?

Msg 102, Level 15, State 1, Line 3922
Incorrect syntax near 'day'

Welcome to stackoverflow. Please take a minute to take the [tour], especially How to [Ask], and [edit] your question accordingly. — Zohar Peled, May 10 '18 at 06:11
Well, which database system are you using? The only tags you've placed are java and c# - neither of these is relevant here, because you haven't shown any java code or c# code. — Dawood ibn Kareem, May 10 '18 at 06:16
The error was Msg 102, Level 15, State 1, Line 3922 Incorrect syntax near 'day'. — Vino Thini, May 10 '18 at 06:27
Sql server you mean microsoft's sql server? There is no such thing as `enum`. It's from MySql. — SᴇM, May 10 '18 at 06:30
yes,microsoft sql server. I was convert the code in online converter from mysql to sql server — Vino Thini, May 10 '18 at 06:34
@VinoThini check out this post: [SQL Server equivalent to MySQL enum data type](https://stackoverflow.com/questions/1434298/sql-server-equivalent-to-mysql-enum-data-type) — SᴇM, May 10 '18 at 06:37
I've retracted my downvote because you've edited the question to include the details that was missing in order to provide an answer. — Zohar Peled, May 10 '18 at 06:44

score 2 · Answer 1 · answered May 10 '18 at 06:41

If we are talking about T-SQL, then the error near day means that the syntax is incorrect (simply there is no enum keyword in T-SQL), it should be something like this:

[frequency] VARCHAR(10) NOT NULL,
CONSTRAINT ck_Enum CHECK ([frequency] IN('day','week','semi_month','month','year')),

Considering the second error you use cast in the wrong context, SQL doesn't know the type of the column. I believe that you wanted to write:

[duration] INT DEFAULT 10 NOT NULL 
CONSTRAINT ck_Duration CHECK ([duration] > 0),

With additional checking whether duration is unsigned. You should obey the language's syntax because you have some more errors. Let the following be an example for you:

CREATE TABLE ec_recurring 
( 
[recurring id]      INT IDENTITY NOT NULL
CONSTRAINT pl_recId PRIMARY KEY, 

[price]             DECIMAL(10, 4) NOT NULL,

[frequency]         VARCHAR(10) NOT NULL,
CONSTRAINT ck_Enum CHECK ([frequency] IN('day','week','semi_month','month','year')), 

[duration]          INT DEFAULT 10 NOT NULL 
CONSTRAINT ck_Duration CHECK ([duration] > 0), 

[cycle]             INT DEFAULT 10 NOT NULL 
CONSTRAINT ck_Cycle CHECK ([cycle] > 0), 

[trial status]      TINYINT DEFAULT 4 NOT NULL, 

[trial price]       DECIMAL(10, 4) NOT NULL, 

[trial frequency]   VARCHAR(10) NOT NULL 
CONSTRAINT ck_TrialEnum CHECK ([trial frequency] IN('day','week','semi_month','month','year')), 

[trial duration]    INT DEFAULT 10 NOT NULL 
CONSTRAINT ck_TrialDuration CHECK ([trial duration] > 0), 

[trial cycle]       INT DEFAULT 10 NOT NULL 
CONSTRAINT ck_TrialCycle CHECK ([trial cycle] > 0), 

[status]            TINYINT DEFAULT 4 NOT NULL, 

[sort order]        INT DEFAULT 11 NOT NULL, 

[date created]      DATETIME2 NOT NULL, 

[date modified]     DATETIME2 NOT NULL, 

[created by]        DATETIME NOT NULL, 

[modified by]       DATETIME NOT NULL, 

[active]            TINYINT DEFAULT 4 NOT NULL, 
);

@VinoThini If an answer solved your problem you should accept it so that other people will know that the problem is solved. — Zohar Peled, May 10 '18 at 07:05

score 1 · Answer 2 · answered May 10 '18 at 06:42

1

In Microsoft's SQL Server is no such thing as enum, it is from MySql, but you can use its equivalent:

mycol VARCHAR(10) NOT NULL CHECK (mycol IN('Useful', 'Useless', 'Unknown'))

Original Post

answered May 10 '18 at 06:42

SᴇM

7,024
3
24
41

Incorrect syntax near 'a' with enum('a','b','c')

2 Answers2