SQL Reference

Chapter 2 | Supported standards 20
CREATE TABLE statement
Use the CREATE TABLE statement to create a table in a database file. The format of the CREATE
TABLE statement is:
CREATE TABLE table_name ( table_element_list [,
table_element_list...] )
Within the statement, you specify the name and data type of each column.
1 table_name is the name of the table. table_name has a 100 character limit. A table with the
same name must not already be defined. The table name must begin with an alphabetic
character. If the table name begins with other than an alphabetic character, enclose it in double
quotation marks (quoted identifier).
1 The format for table_element_list is:
field_name field_type [DEFAULT expr]
[UNIQUE
| NOT NULL | PRIMARY KEY | GLOBAL]
[EXTERNAL relative_path_string [SECURE | OPEN calc_path_string]]
1 field_name is the name of the field. No field in the same table may have the same name.
You specify a field repetition by using a number in square brackets. For example:
lastDates[4]. Field names begin with an alphabetic character. If the field name begins
with other than an alphabetic character, enclose it in double quotation marks (quoted
identifier). For example, the CREATE TABLE statement for the field named _LASTNAME is:
CREATE TABLE "_EMPLOYEE" (ID INT PRIMARY KEY, "_FIRSTNAME"
VARCHAR(20), "_LASTNAME" VARCHAR(20))
1 field_type may be any of the following: NUMERIC, DECIMAL, INT, DATE, TIME,
TIMESTAMP, VARCHAR, CHARACTER VARYING, BLOB, VARBINARY, LONGVARBINARY, or
BINARY VARYING. For NUMERIC and DECIMAL, you can specify the precision and scale.
For example: DECIMAL(10,0). For TIME and TIMESTAMP, you can specify the precision.
For example: TIMESTAMP(6). For VARCHAR and CHARACTER VARYING, you can specify
the length of the string. For example: VARCHAR(255).
1 The DEFAULT keyword allows you to set a default value for a column. For expr, you may
use a constant value or expression. Allowable expressions are USER, USERNAME,
CURRENT_USER, CURRENT_DATE, CURDATE, CURRENT_TIME, CURTIME,
CURRENT_TIMESTAMP, CURTIMESTAMP, and NULL.
1 Defining a column to be UNIQUE automatically selects the Unique Validation Option for the
corresponding field in the FileMaker database file.
1 Defining a column to be NOT NULL automatically selects the Not Empty Validation Option
for the corresponding field in the FileMaker database file. The field is flagged as a Required
Value in the Fields tab of the Manage Database dialog box in FileMaker
Pro.