Introduction

---

The SQL CREATE TABLE statement is used to create a table.

Syntax

---

CREATE TABLE [IF NOT EXISTS] table_reference

(

column_name data type [dimensions]
[DESCRIPTION column description]
[NULLABLE][NOT NULL]
[DEFAULT default value]
[MIN | MINIMUM minimum value]
[MAX | MAXIMUM maximum value]
[CHARCASE UPPER | LOWER | NOCHANGE]
[COMPRESS 0..9]

[, column_name...]

[, [CONSTRAINT constraint_name]
[UNIQUE] [NOCASE]
PRIMARY KEY (column_name [[ASC |ASCENDING] | [DESC | DESCENDING]]
[, column_name...])
[COMPRESS DUPBYTE | TRAILBYTE | FULL | NONE]]
[NOKEYSTATS]

[TEXT INDEX (column_name, [column_name])]
[STOP WORDS space-separated list of words]
[SPACE CHARS list of characters]
[INCLUDE CHARS list of characters]

[DESCRIPTION table_description]

[INDEX PAGE SIZE index_page_size]
[BLOB BLOCK SIZE BLOB_block_size]

[LOCALE locale_name | LOCALE CODE locale_code]

[ENCRYPTED WITH password]

[USER MAJOR VERSION user-defined_major_version]
[USER MINOR VERSION user-defined_minor_version]

[LAST AUTOINC last_autoinc_value]
)

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]
[DESCRIPTION column description]
[NULLABLE][NOT NULL]
[DEFAULT default value]
[MIN or MINIMUM minimum value] [MAX or MAXIMUM maximum value]
[CHARCASE UPPER | LOWER | NOCHANGE]
[COMPRESS 0..9]

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. Please see the Data Types and NULL Support topic for more information.

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 50 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

NOT NULL

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
(
  Last_Name CHAR(20) DESCRIPTION 'Last Name',
  First_Name CHAR(15) DESCRIPTION 'First Name',
  Hire_Date DATE DESCRIPTION 'Hire Date' DEFAULT CURRENT_DATE 
  Salary NUMERIC(10,2) DESCRIPTION 'Salary' DEFAULT 0.00,
  Dept_No SMALLINT DESCRIPTION 'Dept #',
  PRIMARY KEY (Last_Name, First_Name)
)

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]
[UNIQUE] [NOCASE]
PRIMARY KEY (column_name [[ASC |ASCENDING] | [DESC | DESCENDING]]
[, column_name...])
[COMPRESS DUPBYTE | TRAILBYTE | FULL | NONE]]
[NOKEYSTATS]

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]]
[, column_name...])

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. Please see the Index Compression topic for more information.

NOKEYSTATS Clause

---

The NOKEYSTATS clause specifies that the index being defined should not contain any statistics.. The syntax is as follows:

NOKEYSTATS

Under most circumstances you should not specify this clause. Not using the index statistics is only useful for very large tables where insert/update/delete performance is very important, and where it is acceptable to not have logical record numbers or statistics for optimizing filters and queries.

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
(
  Last_Name CHAR(20) DESCRIPTION 'Last Name',
  First_Name CHAR(15) DESCRIPTION 'First Name',
  Hire_Date DATE DESCRIPTION 'Hire Date' DEFAULT CURRENT_DATE 
  Salary NUMERIC(10,2) DESCRIPTION 'Salary' DEFAULT 0.00,
  Dept_No SMALLINT DESCRIPTION 'Dept #',
  NOCASE PRIMARY KEY (Last_Name, First_Name) COMPRESS FULL
)

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])
STOP WORDS space-separated list of words
SPACE CHARS list of characters
INCLUDE CHARS list of characters

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.

For more information on how these clauses work, please see the Full Text Indexing topic.

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. Please see Appendix C - System Capacities for more information on the minimum and maximum index page sizes.

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. Please see Appendix C - System Capacities for more information on the minimum and maximum BLOB block sizes.

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. A list of locale constants and their IDs can be retrieved via the TDBISAMEngine GetLocaleNames method. 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 (''). Please see the Encryption topic for more information.

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 MINOR VERSION user-defined_minor_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.

Please see the Creating and Altering Tables topic for more information on creating tables.