| |
|
|
Introduction
The SQL CREATE TABLE statement is used to create a table.
Syntax CREATE TABLE [IF NOT EXISTS] table_reference Use the CREATE TABLE statement to create a table, define its columns, and define a primary key constraint.
The specified table name must follow DBISAM's SQL naming conventions for tables. Please see the Naming Conventions topic for more information.
Column Definitions
The syntax for defining a column is as follows: column_name data type [dimensions] Column definitions consist of a comma-separated list of combinations of column name, data type and (if applicable) dimensions, and optionally their description, allowance of NULL values, default value, minimum and maximum values, character-casing, and compression level (for BLOB columns). The list of column definitions must be enclosed in parentheses. The number and type of dimensions that must be specified varies with column type.
DESCRIPTION Clause
The DESCRIPTION clause specifies the description for the column. The syntax is as follows: DESCRIPTION column description The description must be enclosed in single or double quotes and can be any value up to 100 characters in length.
NULLABLE and NOT NULL Clauses
The NULLABLE clause specifies that the column is not required and can be NULL. The NOT NULL clause specifies that the column is required and cannot be NULL. The syntax is as follows: NULLABLE DEFAULT Clause
The DEFAULT clause specifies the default value for the column. The syntax is as follows: DEFAULT default value The default value must be a value that matches the data type of the column being defined. Also, the value must be expressed in ANSI/ISO format if it is a date, time, timestamp, or number. Please see the Naming Conventions topic for more information.
MINIMUM Clause
The MINIMUM clause specifies the minimum value for the column. The syntax is as follows: MIN | MINIMUM minimum value The minimum value must be a value that matches the data type of the column being defined. Also, the value must be expressed in ANSI/ISO format if it is a date, time, timestamp, or number. Please see the Naming Conventions topic for more information.
MAXIMUM Clause
The MAXIMUM clause specifies the maximum value for the column. The syntax is as follows: MAX | MAXIMUM maximum value The maximum value must be a value that matches the data type of the column being defined. Also, the value must be expressed in ANSI/ISO format if it is a date, time, timestamp, or number. Please see the Naming Conventions topic for more information.
CHARCASE Clause
The CHARCASE clause specifies the character-casing for the column. The syntax is as follows: CHARCASE UPPER | LOWER | NOCHANGE If the UPPER keyword is used, then all data values in this column will be upper-cased. If the LOWER keyword is used, then all data values in this column will be lower-cased. If the NOCHANGE keyword is used, then all data values for this column will be left in their original form. This clause only applies to string columns and is ignored for all others.
The following statement creates a table with columns that include descriptions and default values: CREATE TABLE employee Primary Index Definition
Use the PRIMARY KEY (or CONSTRAINT) clause to create a primary index for the new table. The syntax is as follows: [, [CONSTRAINT constraint_name] The columns that make up the primary index must be specified. The UNIQUE flag is completely optional and is ignored since primary indexes are always unique. The alternate CONSTRAINT syntax is also completely optional and ignored.
A primary index definition can optionally specify that the index is case-insensitive and the compression used for the index.
NOCASE Clause
The NOCASE clause specifies the that the primary index should be sorted in case-insensitive order as opposed to the default of case-sensitive order. The syntax is as follows: NOCASE Columns Clause
The columns clause specifies a comma-separated list of columns that make up the primary index, and optionally whether the columns should be sorted in ascending (default) or descending order. The syntax is as follows: PRIMARY KEY (column_name [[ASC |ASCENDING] | [DESC | DESCENDING]] The column names specified here must conform to the column naming conventions for DBISAM's SQL and must have been defined earlier in the CREATE TABLE statement. Please see the Naming Conventions topic for more information.
COMPRESS Clause
The COMPRESS clause specifies the type of index key compression to use for the primary index. The syntax is as follows: COMPRESS DUPBYTE | TRAILBYTE | FULL | NONE The DUPBYTE keyword specifies that duplicate-byte index key compression will be used, the TRAILBYTE keyword specifies that trailing-byte index key compression will be used, and the FULL keyword specifies that both duplicate-byte and trailing-byte index key compression will be used. The default index key compression is NONE.
The following statement creates a table with a primary index on the Last_Name and First_Name columns that is case-insensitive and uses full index key compression: CREATE TABLE employee Note Primary indexes are the only form of constraint that can be defined with CREATE TABLE.
Full Text Indexes Definitions
Use the TEXT INDEX, STOP WORDS, SPACE CHARS, and INCLUDE CHARS clauses (in that order) to create a full text indexes for the new table. The syntax is as follows: TEXT INDEX (column_name, [column_name]) When using full text indexing, the TEXT INDEX clause is required and consists of a comma-separated list of columns that should be full text indexed. The column names specified here must conform to the column naming conventions for DBISAM's SQL and must have been defined earlier in the CREATE TABLE statement. Please see the Naming Conventions topic for more information.
The STOP WORDS clause is optional and consists of a space-separated list of words as a string that specify the stop words used for the full text indexes.
The SPACE CHARS and INCLUDE CHARS clauses are optional and consist of a set of characters as a string that specify the space and include characters used for the full text indexes.
Table Description
Use the DESCRIPTION clause to specify a description for the table. The syntax is as follows: DESCRIPTION table_description The description is optional and should be specified as a string.
Table Index Page Size
Use the INDEX PAGE SIZE clause to specify the index page size for the table. The syntax is as follows: INDEX PAGE SIZE index_page_size The index page size is optional and should be specified as an integer in the 1024 bytes to 16 kilobytes range.
Table BLOB Block Size
Use the BLOB BLOCK SIZE clause to specify the BLOB block size for the table. The syntax is as follows: BLOB BLOCK SIZE BLOB_block_size The BLOB block size is optional and should be specified as an integer in the 64 bytes to 64 kilobytes range.
Table Locale
Use the LOCALE clause to specify the locale for the table. The syntax is as follows: LOCALE locale_name | LOCALE CODE locale_code The locale is optional and should be specified as an identifier enclosed in double quotes ("") or square brackets ([]) if specifying a locale constant, or as an integer value if specifying a locale ID. If this clause is not specified, then the default "ANSI Standard" locale (ID 0) will be used for the table.
Table Encryption
Use the ENCRYPTED WITH clause to specify whether the table should be encrypted with a password. The syntax is as follows: ENCRYPTED WITH password Table encryption is optional and the password for this clause should be specified as a string constant enclosed in single quotes ('').
User-Defined Versions
Use the USER MAJOR VERSION and USER MINOR VERSION clauses to specify user-defined version numbers for the table. The syntax is as follows: USER MAJOR VERSION user-defined_major_version User-defined versions are optional and the versions should be specified as integers.
Last Autoinc Value
Use the LAST AUTOINC clause to specify the last autoinc value for the table. The syntax is as follows: LAST AUTOINC last_autoinc_value The last autoinc value is optional and should be specified as an integer. If this clause is not specified, the default last autoinc value is 0.
|
