 |
|
|
 |
CREATE TABLE creates a table with the given name and structure.
Syntax
CREATE TABLE [ IF NOT EXISTS ] [ MEMORY ] table_name
| ( column_definition [, ... ]
|
| index_definition [, ... ]
|
where column_definition is:
| column_name data_type [ autoinc_options ] [ blob_settings ]
|
| [ DEFAULT default_value ]
|
| [ MINVALUE min_value | NOMINVALUE ]
|
| [ MAXVALUE max_value | NOMAXVALUE ]
|
[ { PRIMARY [KEY] | UNIQUE } [ ASC | DESC ] [ CASE | NOCASE ] ]
| [ INCREMENT increment_value ]
|
| [ INITIALVALUE start_value ]
|
| [ BLOBBLOCKSIZE {1..4294967295} ]
|
| [ BLOBCOMPRESSIONALGORITHM {NONE | ZLIB | BZIP | PPM} ]
|
| [ BLOBCOMPRESSIONMODE {1 .. 9} ]
|
and index_definition is:
[, PRIMARY [ KEY ] [ index_name ] ( column_name [ ASC | DESC ] [ CASE | NOCASE ] [, ... ] ) ]
[, [ UNIQUE ] [INDEX] [index_name] ( column_name [ ASC | DESC ] [ CASE | NOCASE ] [, ... ] ) ]
[, ... ]
Description
CREATE TABLE will create a new, initially empty table in the current database.
IF NOT EXISTS
| If this keyword is specified, the table will be created, only if there is no existing table with the same name. If this keyword is not specified and the table already exists, an exception will be raised.
|
MEMORY
| If MEMORY keyword is specified before the table_name then an in-memory table will be created, not a disk one.
|
table_name
| The name of the table to be created.
|
Column Parameters.
column_name
| The name of a column to be created in the new table.
|
data_type
| The data type of the column. See the Field Data Types topic for more details about supported data types.
|
NOT NULL
| The column is not allowed to contain null values.
|
NULL
| The column is allowed to contain null values. This is the default.
|
DEFAULT default_value
| The DEFAULT clause assigns a default data value for the column whose column definition it appears within. The value is any constant expression. The data type of the default expression must match the data type of the column.
|
MINVALUE min_value
| The minimum value for the column. The data type of the min_value expression must match the data type of the column.
|
NOMINVALUE
| The column has no restrictions on minimum value. This is the default.
|
MAXVALUE max_value
| The maximum value for the column. The data type of the max_value expression must match the data type of the column.
|
NOMAXVALUE
| The column has no restrictions on maximum value. This is the default.
|
PRIMARY [KEY]
| The primary key constraint specifies that a column may contain only unique (non-duplicate), nonnull values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of columns as primary key also implies that other tables may rely on this set of columns as a unique identifier for rows.
|
| Only one primary key can be specified for a table, whether as a column constraint or a table constraint.
|
UNIQUE
| The UNIQUE constraint specifies that a column of a table may contain only unique values.
|
| For the purpose of a unique constraint, null values are considered equal.
|
Autoinc Options.
autoinc_data_type
| The data type of the autoinc column: Byte, Integer, LargeInteger, etc.
|
INCREMENT increment_value
| The increment added to the next generated autoinc value.
|
INITIALVALUE start_value
| The value used to initialize autoinc column value.
|
MINVALUE min_value
| The minimum value for the cycled autoinc column. The data type of the min_value expression must match the data type of the column.
|
MAXVALUE max_value
| The maximum value for the cycled autoinc column. The data type of the max_value expression must match the data type of the column.
|
CYCLED
| Autoinc values will cycle. This is the default.
|
NOCYCLED
| Autoinc values will not cycle.
|
Blob Settings
BLOBBLOCKSIZE {1..4294967295}
| The size of the compressed buffer used for Blob data compression.
|
BLOBCOMPRESSIONALGORITHM {NONE | ZLIB | BZIP | PPM}
| Compression algorithm used to compress Blob field.
|
BLOBCOMPRESSIONMODE {1 .. 9}
| The level of compression, 1 - min, 9 - max.
|
Index Parameters
PRIMARY [KEY]
| The primary key constraint specifies that an indexed column(s) may contain only unique (non-duplicate), nonnull values.
|
| Only one primary key can be specified for a table, whether as a column constraint or a table constraint.
|
UNIQUE
| The UNIQUE constraint specifies that an indexed column(s) of a table may contain only unique values. For the purpose of a unique constraint, null values are considered equal.
|
index_name
| The name of an index to be created.
|
column_name
| The name of a column to be indexed.
|
ASC
| The column will be indexed with ascending sort order. This is the default.
|
DESC
| The column will be indexed with descending sort order.
|
CASE
| The column will be indexed with case-sensitive sort order. This is the default.
|
NOCASE
| The column will be indexed with case-insensitive sort order.
|
Examples:
| CREATE TABLE developers (
|
| BlobCompressionAlgorithm ZLib
|
| id AutoInc(Byte, INITIALVALUE 3, INCREMENT 3, MAXVALUE 7 minvalue 2 CYCLED),
|
|
|
