Chapter 2 | Supported standards 21
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.
1 To define a column as a container field, use BLOB, VARBINARY, or BINARY VARYING for
the field_type.
1 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 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
TRUNCATE TABLE statement
Use the TRUNCATE TABLE statement to quickly delete all records in the specified table, emptying
the table of all data.
TRUNCATE TABLE table_name
You cannot specify a WHERE clause with the TRUNCATE TABLE statement. The TRUNCATE TABLE
statement deletes all records.
Only the records in the table specified by table_name are deleted. Records from any related
tables are not affected.
The TRUNCATE TABLE statement needs to be able to lock all records in the table in order to delete
the record data. If any record in the table is locked by another user, FileMaker returns the error
code 301 (“Record is in use by another user”).
Using Sample SQL
text column CREATE TABLE T1 (C1 VARCHAR, C2 VARCHAR (50), C3 VARCHAR (1001),
C4 VARCHAR (500276))
text column, NOT NULL CREATE TABLE T1NN (C1 VARCHAR NOT NULL, C2 VARCHAR (50) NOT NULL,
C3 VARCHAR (1001) NOT NULL, C4 VARCHAR (500276) NOT NULL)
numeric column CREATE TABLE T2 (C1 DECIMAL, C2 DECIMAL (10,0), C3 DECIMAL
(7539,2), C4 DECIMAL (497925,301))
date column CREATE TABLE T3 (C1 DATE, C2 DATE, C3 DATE, C4 DATE)
time column CREATE TABLE T4 (C1 TIME, C2 TIME, C3 TIME, C4 TIME)
timestamp column CREATE TABLE T5 (C1 TIMESTAMP, C2 TIMESTAMP, C3 TIMESTAMP,
C4 TIMESTAMP)
column for container field CREATE TABLE T6 (C1 BLOB, C2 BLOB, C3 BLOB, C4 BLOB)
column for external storage
container field
CREATE TABLE T7 (C1 BLOB EXTERNAL 'Files/MyDatabase/' SECURE)
CREATE TABLE T8 (C1 BLOB EXTERNAL 'Files/MyDatabase/'
OPEN 'Objects')