SQL Create Table Syntax Error on Constraint

Question

While trying to create some tables with constraints I have stumbled on syntax errors. I am using Microsoft Access and it keeps advising me that my second Constraint is wrong. What is going on? My code looks as follows:

CREATE TABLE STORE 
(
StoreName Char(25) NOT NULL,
City Char(35) NULL
Country Char(50) NULL,
Phone Char(8) NULL,
Fax Char(15) NULL,
Email Varchar(100) NULL,
Contact Char(35) NULL,
CONSTRAINT StorePK PRIMARY KEY(StoreName),
CONSTRAINT Citizen CHECK (Country IN ('Belize', 'United States', 'Mexico','China', 'Germany', 'France', 'Netherlands'))
);

I am not aware that MS Access supports check constraints: msdn.microsoft.com/en-us/library/office/…. — Gordon Linoff
– Gordon Linoff, Commented Dec 3, 2015 at 0:57
Possible duplicate of Is it possible to create a check constraint in access and/or DAO? — Didier Aupest
– Didier Aupest, Commented Dec 3, 2015 at 1:04

shawnt00 · Accepted Answer · 2015-12-03 01:07:31Z

1

I believe you have to create the table and the use ALTER TABLE to add the constraint.

ALTER TABLE STORE
CONSTRAINT Citizen CHECK (
    Country IN (
        'Belize', 'United States', 'Mexico','China',
        'Germany', 'France', 'Netherlands'
    )
);

I don't know how much the situation has changed since Access 2000 I'm pretty certain that some limitations still apply as indicated in this old documentation:

Note The check constraint statement can only be executed through the Jet OLE DB provider and ADO; it will return an error message if used though the Access SQL View user interface.

https://msdn.microsoft.com/en-us/library/aa140015%28office.10%29.aspx#acintsql_ddlconst

edited Dec 3, 2015 at 1:07

answered Dec 3, 2015 at 1:01

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HansUp · Accepted Answer · 2015-12-03 01:29:30Z

Your CREATE TABLE statement is valid Access DDL when executed from ADO/OleDb. CHECK constraints are among the DDL features added with Jet 4, and which are not supported under DAO.

That also means CHECK is not supported by default for queries run from the query designer. You might be able to work around that limitation by setting the Access option "SQL Server Compatible Syntax (ANSI 92)". However that option has other side effects. If you use it, make sure to test your existing queries to see whether they all still operate as intended.

I put your statement text in a variable and executed it successfully like this:

CurrentProject.Connection.Execute strDDL

That worked because CurrentProject.Connection is an ADO object.

Collectives™ on Stack Overflow

SQL Create Table Syntax Error on Constraint

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related