ToC path:

SQL Create Table

An SQL table, which corresponds to an eXtremeDB class, is created using the SQL create table statement specifying the table name and column (field) definitions. For example:
 
    create table t (x integer);
     
Here the table (class) t is created with a single column (field) x of type integer.

The syntax is as follows:
 
    CREATE [temporary | local] TABLE name "(" { column_name data_type [ column constraint [ ... ] ] | 
                        [ CONSTRAINT name ] table_constraint } [ "," ... ] ")"
                        [IF NOT EXISTS]
 
    column_constraint: 
        [ NOT ] NULL | PRIMARY KEY | UNIQUE | 
        USING [ HASH | RTREE ] INDEX | 
        [ FOREGN KEY ] REFERENCES table_name [ "(" column_name [ "," ... ]   ")"
     
    table_constraint:
        | UNIQUE "("  column_name [ "," ... ]  ")"
        | PRIMARY KEY "("  column_name [ "," ... ]  ")"
        | FOREIGN KEY REFERENCES table_name "("  column_name [ "," ... ]  ")"
 
    data_type: 
        BIT | BOOLEAN | CHAR [ "(" size ")" ] | VARCHAR [ "(" size ")" ] | LOGNVARCHAR [ "(" size ")" ] | 
        STRING [ (" width ")" ] | UNICODE[ (" width ")" ]  -- corresponds to eXtremeDB nstring or nchar
        | TINYINT    -- signed<1>
        | SMALLINT -- signed<2>
        | INT [ "(" n ")" ]      -- where n=1,2,4,8 corresponds to eXtremeDB signed<n>
        | INTEGER [ "(" n ")" ]      -- where n=1,2,4,8 corresponds to eXtremeDB signed<n>
        | UNSIGNED [ "(" n ")" ]      -- where n=1,2,4,8 corresponds to eXtremeDB unsigned<n>
        | BIGINT -- signed<8>
        | FLOAT
        | REAL -- double
        | DOUBLE
        | NUMERIC [ "(" width [ "," precision ]  ")" ]  -- eXtremeDB numeric< width,precision> type
        | DECIMAL [ "(" width [ "," precision ]  ")" ]   -- eXtremeDB numeric< width,precision> type
        | DATE -- eXtremeDB 8-byte datetime type
        | TIME   -- eXtremeDB 8-byte datetime type
        | TIMESTAMP  -- eXtremeDB 8-byte datetime type
        | BIANRY [ "(" size ")" ]  --- array or vector of signed<1>
        | VARBINARY  [ "(" size ")" ]   --- array or vector of signed<1>
        | LONGVARBINARY  [ "(" size ")" ]   --- array or vector of signed<1>
        | REFERENCE
        | BLOB
        | ARRAY "(" data_type [ "," length ] ")"
        | SEQUENCE "(" scalar_data_type [ ASC | DESC ] ")"
        | user-defined-type  -- declared by "create domain"
         
Note that all SQL keywords are case insensitive in eXtremeSQL - i.e. "CREATE TABLE" and "create table" are equivalent.

The optional keyword temporary can be used to create temporary or virtual tables. Please see page eXtremeSQL Virtual Tables for further details.

When creating tables it is also necessary to reserve space for the dictionary and newly created tables in the database header. This is done by specifying ddl_dict_size, max_classes and max_indexes in the mco_db_params_t structure passed to mco_db_open_dev() if using the C/C++ API. For example :
     
    db_params.ddl_dict_size = 16*1024;
    db_params.max_classes = 100;
    db_params.max_indexes = 100;
     
If using xSQL, these parameters can be set with config file parameters:
 
    db_params: 
    {
        ddl_dict_size: 16K,
        max_classes : 100,
        max_indexes : 100,
    }
     
Local Tables

The local qualifier designates the table for eXtremeDB High Availability partial replication. Tables can be declared local for the master or replica database to keep data in these tables from being replicated across the network.

If Not Exists

The if not exists clause can be used to override an error when the table already exists. For example, note how the following create table statement fails while adding the if not exists clause allows the execution to succeed:
 
    XSQL>create table foo(x integer);
    XSQL>create table foo(x integer, y integer);
    ERROR: Table already exists
    XSQL>create table if not exists foo(x integer, y integer);
    XSQL>insert into foo values (1);
    XSQL>select * from foo;
    x
    ------------------------------------------------------------------------------
    1
     
    Selected records: 1
     
Constraint interpretation

Constraints can be specified for the entire table or for individual columns. If a table constraint is explicitly assigned a name (using the CONSTRAINT name clause) then this name is used as the index name. Otherwise the name of first constraint field is used as the index name.

If no index is specified by means of constraints, then a list index (actually a hash) is implicitly created for this class.

[Not] Null

In the case of column NULL constraint, the eXtremeDB field is marked as nullable (a null-indicator is explicitly created for this field).

Note that:
1. The scope of this command is connection (SQL session)

2. Nullable fields add storage overhead; one byte per field. Nullable fields cannot be indexed, which can greatly affect performance by removing index-based optimizations. For instance, given this default policy, and the following CREATE TABLE statement:
 
    create table foo(x integer using index);
     
An index will not be used to execute
 
    select * from foo order by x;
     
3. This default policy allows third-party tools to connect to an eXtremeSQL database via JDBC and ODBC as these tools rely on the specified SQL standard to access databases.
Unique and Primary Key

For UNIQUE and PRIMARY KEY a unique index is created. The USING [HASH] INDEX and FOREIGN KEY constraints cause creation of a non-unique index. The column constraint USING INDEX can be combined with UNIQUE - in this case the index will be also declared as unique.

In the case of the USING HASH INDEX constraint, an eXtremeDB hash table index is used, otherwise a B-Tree index is used. The initial size of the hash table is set to 100000 (this hash table will be dynamically reallocated if the number of records in the table becomes larger than this value).

Varying vs. Fixed Size types

The CHAR(N) specification maps to the eXtremeDB fixed length character array type (char<N> or nchar<N>). The VARCHAR, LONGVARCHAR, STRING or UNICODE types are mapped to the eXtremeDBstring or nstring type; if followed by a size specification, the size specification is ignored.

The BINARY(N) specification is mapped to the fixed size binary eXtremeDB type (binary<n>). The VARBINARY or LONGVARBINARY types are mapped to the eXtremeDB variable size binary type (varbinary); if followed by a size specification, the size specification is ignored. Note that, unlike the CHAR(N)and VARCHAR types, BINARY(N) and VARBINARY types are not passed with zeros and do not interpret null character (\0) as the string terminator.

Please see the Base Data Types page for definitions of the eXtremeDB data types.

User-defined Types

eXtremeSQL supports the create domain statement. Domains can be used as session level type aliases. However note that eXtremeDB does not persist the domain definition (i.e. once the session is terminated the domain definition is lost).

The syntax is very simple:
 
    CREATE DOMAIN name type;
     
where type is any valid type definition, for example char(10).