Create a table (SQL in MS Access)

Question

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
) 

Answer 1

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

Answer 2

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.