Access Database Design and Programming, Second Edition

The DDL Component of Access SQL

We begin by looking at the data definition commands in Access SQL. These commands do not have a counterpart in Query Design View (although, of course, you can perform these functions through the Access graphical environment). Access SQL supports these four DDL commands:

• CREATE TABLE

• ALTER TABLE

• DROP TABLE

• CREATE INDEX

We should mention now that there is some duplication of features in the DDL commands. For instance, you can add an index to a table using either the ALTER TABLE command or the CREATE INDEX command.

The CREATE TABLE Statement

The CREATE TABLE command has the following syntax:

CREATE TABLE TableName 
 (ColumnDefinition,...
 [,Multi-ColumnConstraint,...] );

In words, the parameters to the CREATE TABLE statement are a table name, followed by one or more column definitions, followed by one or more (optional) multicolumn constraints. Note that the parentheses are also part of the syntax.

Column definition

A column definition is defined as follows:

ColumnDefinition ::= ColumnName 
                      DataType[(Size)]
                          [Single-ColumnConstraint]

In words, a ColumnDefinition is a ColumnName, followed by a DataType (with size if appropriate), followed by a Single-ColumnConstraint.

There are several data types available in Access SQL. For comparison, the list in Table 6.2 includes the corresponding selection in the Access Table Design window. (We have not included all synonyms for the data types.) Note that the SQL type INTEGER corresponds with the Access data type Long. Note also ...