Doesn't run, says Syntax error in a constraint clause

CREATE TABLE Project
(
    ProjectID INTEGER NOT NULL,
    Name VARCHAR(25) NOT NULL,
    DepartmentName VARCHAR(20) NOT NULL, 
    MaxHours NUMERIC NOT NULL,
    StartDate DateTime,
    EndDate DateTime,

    CONSTRAINT PK_ProjectID PRIMARY KEY (ProjectID),
    CONSTRAINT FK_DepartmentName 
        FOREIGN KEY (DepartmentName) REFERENCES Department (DepartmentName) 
            ON UPDATE CASCADE 
            ON DELETE NO ACTION
) 

Doesn't run, says Syntax error in a constraint clause

CREATE TABLE Project
(
    ProjectID INTEGER NOT NULL,
    Name VARCHAR(25) NOT NULL,
    DepartmentName VARCHAR(20) NOT NULL, 
    MaxHours NUMERIC NOT NULL,
    StartDate DateTime,
    EndDate DateTime,

    CONSTRAINT PK_ProjectID PRIMARY KEY (ProjectID),
    CONSTRAINT FK_DepartmentName 
        FOREIGN KEY (DepartmentName) REFERENCES Department (DepartmentName) 
            ON UPDATE CASCADE 
            ON DELETE NO ACTION
) 

• sql
• ms-access
• syntax-error
• constraints

• 2 This looks like you are using SQL Server sql syntax not MSAccess sql syntax. Most database share a minimalistic "core" of the sql language (aka "the sql standard") but in reality every sql engine is slightly different. You have to consult an MSAccess sql reference when you write sql for MSAccess. – topsail Commented Nov 21, 2024 at 0:43
• Also FWIW most MSAccess developers do not create table with sql scripts. Just use the MSAccess table designer - its very easy to work with and a I think is still a little better for obscure reasons not worth going into here. – topsail Commented Nov 21, 2024 at 0:46
• Agree with above. Why are you using SQL to create table in Access? – June7 Commented Nov 21, 2024 at 5:42
• What about autonumber PK? Should only save FK_DepartmentID not DepartmentName. Name is a reserved word - should not use reserved words as names. ProjectName would be better. – June7 Commented Nov 21, 2024 at 6:13

2 Answers 2

As you use Access, avoid the mess with the limited SQL syntax.

Use the power of DAO to create your table and relation:

Public Function CreateProjectTable()

    Dim Database    As DAO.Database
    Dim Table       As DAO.TableDef
    Dim TableParent As DAO.TableDef
    Dim Field       As DAO.Field
    Dim Index       As DAO.Index
    Dim Relation    As DAO.Relation
    
    Dim Attributes  As Long
    
    Set Database = CurrentDb
    
    ' Table.
    
    Set Table = Database.CreateTableDef("Project")
    
    ' Fields.
  
    Set Field = Table.CreateField("ProjectID", dbInteger)
    Field.Required = True
    Table.Fields.Append Field
    
    Set Field = Table.CreateField("Name", dbText, 25)
    Field.Required = True
    Field.AllowZeroLength = False
    Table.Fields.Append Field
    
    Set Field = Table.CreateField("DepartmentName", dbText, 20)
    Field.Required = True
    Field.AllowZeroLength = False
    Table.Fields.Append Field
    
    Set Field = Table.CreateField("MaxHours", dbDouble)
    Field.Required = True
    Table.Fields.Append Field
    
    Set Field = Table.CreateField("StartTime", dbDate)
    Table.Fields.Append Field
    
    Set Field = Table.CreateField("EndTime", dbDate)
    Table.Fields.Append Field
    
    ' Indexes.
  
    Set Index = Table.CreateIndex("PK_ProjectID")
    Index.Primary = True
    Index.Unique = True
    Set Field = Index.CreateField("ProjectID")
    Index.Fields.Append Field
    Table.Indexes.Append Index
    
    Set Index = Table.CreateIndex("FK_DepartmentName")
    Index.Primary = False
    Index.Unique = True
    Set Field = Index.CreateField("DepartmentName")
    Index.Fields.Append Field
    Table.Indexes.Append Index
    
    ' Relation.
    
    Set TableParent = Database.TableDefs("Department")
    Attributes = dbRelationUpdateCascade
    
    Set Relation = Database.CreateRelation("Department_Project")
    Relation.Table = TableParent.Name
    Relation.ForeignTable = Table.Name
    Relation.Attributes = Attributes
    
    Set Field = Relation.CreateField("DepartmentName")
    Field.ForeignName = "DepartmentName"
    Relation.Fields.Append Field
    
    ' Save.
    
    Database.TableDefs.Append Table
    Database.Relations.Append Relation
    
    ' Clean up.
    
    Set Relation = Nothing
    Set Field = Nothing
    Set Table = Nothing
    Set TableParent = Nothing
    Set Database = Nothing

End Function

There is an issue with your SQL statement: MS Access doesn't support ON UPDATE CASCADE and ON DELETE NO ACTION in the FOREIGN KEY constraint.

In MS Access, you can set these options only through the user interface, not through SQL.

You can create the table without these options and then set them manually in the MS Access user interface. Here's how to create the table:

CREATE TABLE Project
(
    ProjectID INTEGER NOT NULL,
    Name VARCHAR(25) NOT NULL,
    DepartmentName VARCHAR(20) NOT NULL, 
    MaxHours NUMERIC NOT NULL,
    StartDate DateTime,
    EndDate DateTime,

    CONSTRAINT PK_ProjectID PRIMARY KEY (ProjectID),
    CONSTRAINT FK_DepartmentName 
        FOREIGN KEY (DepartmentName) REFERENCES Department (DepartmentName)
) 

Then, you can set the CASCADE UPDATE and NO ACTION ON DELETE options manually in the MS Access table relationships user interface.