ODBC and JDBC Guide

44 FileMaker ODBC and JDBC Guide
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 [DEFAULT expr][UNIQUE][NOT NULL]
Within the statement, you specify the name and data type of each column.
1 table_name and table_element_list have a 100 character limit.
1 The DEFAULT keyword allows you to set a default value for a column. You may use a literal such as
CURRENT_USER, CURRENT_DATE, CURRENT_TIME, or CURRENT_TIMESTAMP.
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.
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
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.
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)