# 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 c`reate 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 eXtremeDB`string` 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)`.