Databases Reference
In-Depth Information
I 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.
6.6.1 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.
6.6.1.1 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 that the
Size
option affects only TEXT
columns, indicating the length of the field. (If it is omitted, the text length defaults to
255.)
Table 6-2. Access SQL dat
a
types
SQL data type
Table Design field type
B
OOLEAN, LOGICAL, or YES/NO
Y
es/No
BYTE or INTEGER1
Number, Field Size = Byte
C
OUNTER or AUTOINCREMENT
A
utoNumber, Field Size = Long Integer
CURRENCY or MONEY
Currency
D
ATETIME, DATE, or TIME
D
ate/Time
SHORT, INTEGER2, or SMALLINT
Number, Field Size = Integer
L
ONG, INT, INTEGER, or INTEGER4
N
umber, Field Size = Long
SINGLE, FLOAT4, or REAL
Number, Field Size = Single
D
OUBLE, FLOAT, FLOAT8, NUMBER, or NUMERIC
N
umber, Field Size = Double
TEXT, ALPHANUMERIC, CHAR, CHARACTER, or
S
TRING
Text
