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:

  Copy

  field_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).

  Example

  The CREATE TABLE statement for the field named _LASTNAME is:

  Copy

  CREATE TABLE "_EMPLOYEE" (ID INT PRIMARY KEY, "_FIRSTNAME" VARCHAR(20), "_LASTNAME" VARCHAR(20))

• For the CREATE TABLE statement repetitions, specify a field repetition by using a number from 1 to 32000 in brackets after the field type.

  Example

  Copy

  EMPLOYEE_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, 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.

  Example

  Copy

  VARCHAR(255)

• 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.

• 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, or BINARY VARYING for the field_type.

• To define a column as a container field that stores data externally, use the EXTERNAL keyword. The relative_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 either SECURE for secure storage or OPEN for open storage. If you are using open storage, the calc_path_string is the folder inside the relative_path_string folder where container objects are to be stored. The path must use forward slashes (/) in the folder name.

CREATE TABLE T1 (C1 VARCHAR, C2 VARCHAR (50), C3 VARCHAR (1001), C4 VARCHAR (500276))

CREATE TABLE T1NN (C1 VARCHAR NOT NULL, C2 VARCHAR (50) NOT NULL, C3 VARCHAR (1001) NOT NULL, C4 VARCHAR (500276) NOT NULL)

CREATE TABLE T2 (C1 DECIMAL, C2 DECIMAL (10,0), C3 DECIMAL (7539,2), C4 DECIMAL (497925,301))

CREATE TABLE T3 (C1 DATE, C2 DATE, C3 DATE, C4 DATE)

CREATE TABLE T4 (C1 TIME, C2 TIME, C3 TIME, C4 TIME)

CREATE TABLE T5 (C1 TIMESTAMP, C2 TIMESTAMP, C3 TIMESTAMP, C4 TIMESTAMP)

CREATE TABLE T6 (C1 BLOB, C2 BLOB, C3 BLOB, C4 BLOB)

CREATE TABLE T7 (C1 BLOB EXTERNAL 'Files/MyDatabase/' SECURE) 
CREATE TABLE T8 (C1 BLOB EXTERNAL 'Files/MyDatabase/' OPEN 'Objects')