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 22
ALTER TABLE statement
Use the ALTER TABLE statement to change the structure of an existing table in a database file.
You can modify only one column in each statement. The formats of the ALTER TABLE statement
are:
ALTER TABLE table_name ADD [COLUMN] column_definition
ALTER TABLE table_name DROP [COLUMN] unqualified_column_name
ALTER TABLE table_name ALTER [COLUMN] column_definition SET DEFAULT expr
ALTER TABLE table_name ALTER [COLUMN] column_definition DROP DEFAULT
You must know the table’s structure and how you want to modify it before using the ALTER TABLE
statement.
Examples
Note SET DEFAULT a
nd DROP DEFAULT do not affect existing rows in the table, but change the
default value for rows that are subsequently added to the table.
CREATE INDEX statement
Use the CREATE INDEX statement to speed searches in your database file. The format of the
CREATE INDEX statement is:
CREATE INDEX ON table_name.column_name
CREATE INDEX ON table_name (column_name)
CREATE INDEX is supported for a single column (multi-column indexes are not supported).
Indexes are not allowed on columns that correspond to container field types, summary fields,
fields that have the global storage option, or unstored calculation fields in a FileMaker database
file.
Creating an index for a text column automatically selects the St
orage Option of Minimal in
Indexing for the corresponding field in the FileMaker database file. Creating an index for a non-
text column (or a column formatted as Japanese text) automatically selects the Storage Option of
All in Indexing for the corresponding field in the FileMaker database file.
Creating an index for any column automatically selects the Stora
ge Option of Automatically
create indexes as needed in Indexing for the corresponding field in the FileMaker database file.
FileMaker automatically creates ind
exes as needed. Using CREATE INDEX causes the index to
be built immediately rather than on demand.
Example
CREATE INDEX ON Salespeople.Salesperson_ID
To Sample SQL
add columns ALTER TABLE Salespeople ADD C1 VARCHAR
remove columns ALTER TABLE Salespeople DROP C1
set the default value for a
column
ALTER TABLE Salespeople ALTER Company SET DEFAULT 'FileMaker'
remove the default value
for a column
ALTER TABLE Salespeople ALTER Company DROP DEFAULT