Accuracer: The First And Only BDE Alternative
Client-Server Single-File Embedded Database for Delphi / C++Builder / Kylix
Introduction
The CREATE TABLE statement is used to create a new table.
Syntax
CREATE TABLE table_name (
column_name data_type [(dimensions)] |
AutoInc[([data_type ]
[ , INCREMENT integer ]
[ , STARTVALUE integer ]
[ , MAXVALUE integer | NOMAXVALUE ]
[ , MINVALUE integer | NOMINVALUE ]
[ , CYCLED | NOCYCLED ]
)]
[ BLOBBLOCKSIZE {1..4294967295} ]
[ BLOBCOMPRESSIONALGORITHM {NONE | ZLIB | BZIP | PPM} ]
[ BLOBCOMPRESSIONMODE {0 .. 9} ]
[ DEFAULT {const | NULL} ]
[ NOT NULL | NULL ]
[ PRIMARY [KEY] | UNIQUE [ ASC | DESC ] [ CASE | NOCASE ]]
[MINVALUE value | NOMINVALUE ]
[MAXVALUE value | NOMAXVALUE ]
[,column_name …]
[ , PRIMARY KEY [key name] (column_name [ ASC | DESC ] [ CASE | NOCASE ]
[ {, column_name [ ASC | DESC ] [ CASE | NOCASE ] } ...] ) ]
[ , FOREIGN KEY [key name] (column_name [ { ,column_name } ... ])
REFERENCES table_name [MATCH FULL | MATCH PARTIAL]
[ON DELETE <CASCADE | SET NULL | SET DEFAULT | NO ACTION>]
[ON UPDATE <CASCADE | SET NULL | SET DEFAULT | NO ACTION>] ]
)
table_name = [MEMORY] name_of_the_table.
Use CREATE TABLE to create table with specified structure.
The table_name is the name of the table to be created. If MEMORY option is specified then an in-memory table will be created.
The column_name is a name of the column. The data_type can be one of the following:
Value
|
Description
|
Corresponding TFieldType
|
Char, FixedChar
|
Fixed character field
|
ftFixedChar
|
Varchar, Varchar2, String
|
Character or variable length string field
|
ftString
|
WideChar, FixedWideChar
|
Fixed wide character field
|
ftWideString
|
WideVarchar, WideString
|
Wide character or variable length wide string field
|
ftWideString
|
Shortint, SignedInt8
|
8-bit integer field
|
ftSmallint
|
Smallint, SignedInt16
|
16-bit integer field
|
ftSmallint
|
Integer, SignedInt32
|
32-bit integer field
|
ftInteger
|
Largeint, Int64, SignedInt64
|
64-bit integer field
|
ftLargeint
|
Byte, UnsignedInt8
|
Byte field
|
ftWord
|
Word, UnsignedInt16
|
16-bit unsigned integer field
|
ftWord
|
Cardinal, UnsignedInt32
|
32-bit unsigned integer field
|
ftLargeint
|
AutoInc, AutoincInteger
|
Auto-incrementing 32-bit integer counter field
|
ftAutoinc
|
AutoIncShortint
|
Auto-incrementing 8-bit integer counter field
|
ftAutoinc
|
AutoIncSmallint
|
Auto-incrementing 16-bit integer counter field
|
ftAutoinc
|
AutoIncLargeint
|
Auto-incrementing 64-bit integer counter field
|
ftAutoinc
|
AutoIncByte
|
Auto-incrementing byte counter field
|
ftAutoinc
|
AutoIncWord
|
Auto-incrementing 16-bit unsigned integer counter field
|
ftAutoinc
|
AutoIncCardinal
|
Auto-incrementing 32-bit unsigned integer counter field
|
ftAutoinc
|
Single
|
Single floating-point numeric field
|
ftFloat
|
Float, Double
|
Double floating-point numeric field
|
ftFloat
|
Extended
|
Extended floating-point numeric field
|
ftFloat
|
Boolean, Logical, Bool, Bit
|
Boolean field
|
ftBoolean
|
Currency, Money
|
Money field
|
ftCurrency
|
Date
|
Date field
|
ftDate
|
Time
|
Time field
|
ftTime
|
DateTime
|
Date and time field
|
ftDateTime
|
TimeStamp
|
Date and time field accessed through dbExpress
|
ftTimeStamp
|
Bytes
|
Fixed number of bytes (binary storage)
|
ftBytes
|
VarBytes
|
Variable number of bytes (binary storage)
|
ftVarBytes
|
Blob
|
Binary Large OBject field
|
ftBlob
|
Graphic
|
Bitmap field
|
ftGraphic
|
Memo, Clob
|
Text memo field
|
ftMemo
|
FormattedMemo, FmtMemo
|
Formatted text memo field
|
ftFmtMemo
|
WideMemo, WideClob
|
Unicode text memo field
|
ftMemo
|
The dimensions is a size of the column value in bytes. Use it with bytes, string or wide string data types.
Use NOT NULL option to specify columns with required not empty values.
Specify compression level for storing BLOB field values (BLOB,FmtMemo,Memo,Graphic) via BlobCompressionAlgorithm and BlobCompressionMode options.
The BlobBlockSize is the size in bytes of BLOB data block which is used by database engine in read / write operations with BLOB fields. Minimum value 1 byte, default value 100 Kb.
Here is an example:
CREATE TABLE Test
(
ID AutoInc PRIMARY KEY,
Text String(500),
Numeric Float,
Money Currency,
CurrentDate Date,
Picture Graphic BlobCompressionAlgorithm ZLIB BlobCompressionMode 1
);
Note:
If the table with the specified name already exists, CREATE TABLE will raise an exception.
Table, column and index names can be specified in square brackets ([]). Thus you can use reserved words (like TABLE) and special symbols (like ' ') in table, column and index names.
|