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.
-
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 or contains a period (.), enclose it in double quotation marks (quoted identifier). -
The format for
table_element_list
is:Copyfield_name field_type [[repetitions]]
[DEFAULT expr] [UNIQUE | NOT NULL | PRIMARY KEY | GLOBAL]
[EXTERNAL relative_path_string [SECURE | OPEN calc_path_string]] -
field_name
is the name of the field. Field names must be unique. Field names begin with an alphabetic character. If the field name begins with other than an alphabetic character or contains a period (.), enclose it in double quotation marks (quoted identifier).The
CREATE TABLE
statement for the field named _LASTNAME
is:CopyCREATE TABLE "_EMPLOYEE" (ID INT PRIMARY KEY, "_FIRSTNAME" VARCHAR(20), "_LASTNAME" VARCHAR(20))
-
For the
CREATE TABLE
statementrepetitions
, specify a field repetition by using a number from 1 to 32000 in brackets after the field type.CopyEMPLOYEE_ID INT[4]
LASTNAME VARCHAR(20)[4] -
field_type
may be any of the following:NUMERIC
,DECIMAL
,INT
,DATE
,TIME
,TIMESTAMP
,VARCHAR
,CHARACTER VARYING
,BLOB
,VARBINARY
,LONGVARBINARY
, orBINARY VARYING
. ForNUMERIC
andDECIMAL
, you can specify the precision and scale. For example:DECIMAL(10,0)
. ForTIME
andTIMESTAMP
, you can specify the precision. For example:TIMESTAMP(6)
. ForVARCHAR
andCHARACTER VARYING
, you can specify the length of the string.CopyVARCHAR(255)
-
The
DEFAULT
keyword allows you to set a default value for a column. Forexpr
, you may use a constant value or expression. Allowable expressions areUSER
,USERNAME
,CURRENT_USER
,CURRENT_DATE
,CURDATE
,CURRENT_TIME
,CURTIME
,CURRENT_TIMESTAMP
,CURTIMESTAMP
, andNULL
. -
Defining a column to be
UNIQUE
automatically selects the Unique Validation Option for the corresponding field in the FileMaker Pro database file. -
Defining a column to be
NOT NULL
automatically selects the Not Empty Validation Option for the corresponding field in the FileMaker Pro database file. The field is flagged as a Required Value in the Fields tab of the Manage Database dialog box in FileMaker Pro. -
To define a column as a container field, use
BLOB
,VARBINARY
, orBINARY VARYING
for thefield_type
. -
To define a column as a container field that stores data externally, use the
EXTERNAL
keyword. Therelative_path_string
defines the folder where the data is stored externally, relative to the location of the FileMaker Pro database. This path must be specified as the base directory in the FileMaker Pro Manage Containers dialog box. You must specify eitherSECURE
for secure storage orOPEN
for open storage. If you are using open storage, thecalc_path_string
is the folder inside therelative_path_string
folder where container objects are to be stored. The path must use forward slashes (/) in the folder name.
Using |
Sample SQL |
text column |
Copy
|
text column, |
Copy
|
numeric column |
Copy
|
date column |
Copy
|
time column |
Copy
|
timestamp column |
Copy
|
column for container field |
Copy
|
column for external storage container field |
Copy
|