SQL Reference
Table Of Contents
- Chapter 1 Introduction
- Chapter 2 Supported standards
- Support for Unicode characters
- SQL statements
- SELECT statement
- SQL clauses
- FROM clause
- WHERE clause
- GROUP BY clause
- HAVING clause
- UNION operator
- ORDER BY clause
- OFFSET and FETCH FIRST clauses
- FOR UPDATE clause
- DELETE statement
- INSERT statement
- UPDATE statement
- CREATE TABLE statement
- TRUNCATE TABLE statement
- ALTER TABLE statement
- CREATE INDEX statement
- DROP INDEX statement
- SQL expressions
- SQL functions
- FileMaker system objects
- Reserved SQL keywords
- Index
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 [[repetitions]]
[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. Field names must be unique. 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).
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 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
EMPLOYEE_ID INT[4]
LASTNAME VARCHAR(20)[4]
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.
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.