CREATE TABLE statement

Use the CREATE TABLE statement to create a table in a database file. The format of the CREATE TABLE statement is:

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

Examples

Using

Sample SQL

text column

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

text column, NOT NULL

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

numeric column

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

date column

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

time column

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

timestamp column

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

column for container field

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

column for external storage container field

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