MS Access 10 - syntax error with constraints-CodePudding

I want to write a SQL query in MS Access 2010 for creating a table such that an attribute 'day' should have a value within a constraint I give using 'check' keyword. The check constraint is giving an error on saving the query.

I wrote a SQL query in MS Access 2010 as follows-

CREATE TABLE timeslot
(
    time_slot_id varchar (4),
    day varchar (1) check (day in ('M', 'T', 'W','R', 'F', 'S', 'U')),
    start_time time,
    end_time time,

    primary key (time_slot_id, day, start_time)
);

But on saving the query, I get an error pop-up saying

SYNTAX ERROR IN CREATE TABLE STATEMENT

and it points at the check keyword. When I delete the check constraint, it runs successfully and the table timeslot is created without the constraint applied on 'day' attribute. Am I syntactically wrong in using 'check'? How do I apply the constraint on 'day' attribute?

CodePudding user response：

Day is a reserved word and that name caused another syntax error in my test. If you can't use a different name, enclose it in square brackets as below.

Since your issue is about a check constraint, I ignored the other fields. So this is what worked for me in Access 2010:

Set cn = CurrentProject.Connection
strCreate = "CREATE TABLE timeslot ([day] varchar(1), " & _
    "CONSTRAINT allowed_days CHECK ([day] IN ('M','T','W','R','F','S','U')))"
cn.Execute strCreate

Note a check constraint is only useable in Access DDL for statements executed in ANSI 92 mode. My example worked because CurrentProject.Connection executes statements in ANSI 92 mode.

Later, if you are not fully satisfied with a check constraint for your field, consider using the field's Validation Rule property instead.

strCreate = "CREATE TABLE timeslot ([day] varchar(1))"
cn.Execute strCreate
strRule = "In ('M','T','W','R','F','S','U')"
Set db = CurrentDb
db.TableDefs("timeslot").Fields("day").ValidationRule = strRule

CodePudding user response：

CHECK is invalid there, you can move into its own clause (after a comma):

CREATE TABLE timeslot
(
    time_slot_id varchar (4),
    day varchar (1),
    start_time time,
    end_time time,

    primary key (time_slot_id, day, start_time),
    check (day in ('M', 'T', 'W','R', 'F', 'S', 'U'))
);

EDIT

Alternatively you can specify day to be enum, like

CREATE TABLE timeslot
(
    time_slot_id varchar (4),
    day enum('M', 'T', 'W','R', 'F', 'S', 'U'),
    start_time time,
    end_time time,

    primary key (time_slot_id, day, start_time)
);