ODBC and JDBC Guide

Chapter 5
|
Supported standards 35
Examples
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
You must know the table’s structure and how you want to modify it before using the ALTER TABLE statement.
Examples
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 [ index_name ][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 Storage 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 Storage Option of Automatically create indexes
as needed in Indexing for the corresponding field in the FileMaker database file.
Example
CREATE INDEX myIndex ON Salespeople.Salesperson_ID
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)
BLOB column
CREATE TABLE T6 (C1 BLOB, C2 BLOB, C3 BLOB, C4 BLOB)
To Sample SQL
add columns
ALTER TABLE Salespeople ADD C1 VARCHAR
remove columns
ALTER TABLE Salespeople DROP C1