Delphi Database, Delphi Components from ComponentAce
Products Download Order Contact us
CREATE TABLE Command
Previous  Top  Next



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 ]  
[ NOT NULL | NULL ]  
[ DEFAULT default_value ]  
[ MINVALUE min_value | NOMINVALUE ]  
[ MAXVALUE max_value | NOMAXVALUE ]  
[ { PRIMARY [KEY] | UNIQUE } [ ASC | DESC ] [ CASE | NOCASE ] ]

autoinc_options:  
( [ autoinc_data_type ]  
[ INCREMENT increment_value ]  
[ INITIALVALUE start_value ]  
[ MAXVALUE max_value ]  
[ MINVALUE min_value ]  
[ CYCLED | NOCYCLED ]  
)  
 
blob_settings:  
[ 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 (  
ID AutoInc,  
Code Integer,  
Name Char(20),  
Birthday Date,  
Photo Graphic  
BlobCompressionMode 1  
BlobBlockSize 1024  
BlobCompressionAlgorithm ZLib  
);  
 
CREATE TABLE test (  
id AutoInc(Byte, INITIALVALUE 3, INCREMENT 3, MAXVALUE 7 minvalue 2 CYCLED),  
s String(100)  
);  
        © 2003 - 2024 ComponentAce  | .net zip component | barcode for .net | delphi zip component | delphi database Dec 04, 2024