Creating a New Table

Use the SQL DDL command CREATE TABLE to create a new table in the data grid.

1. Compose a string with the following format:

   CREATE TABLE [ IF NOT EXISTS ] <table_name> (
               <column_name> <column_type> <column_constraint>
               [, <column_name> <column_type> <column_constraint>]...
               [, <table_constraint>] )
               [ <property_name>=<property_value> 
               [, <property_name>=<property_value>]... ]

   Where:

   <column_constraint> = [ [CONSTRAINT <constraint_name>] NOT NULL | NULL | PRIMARY KEY ]
   <column_type> = (see SQL Data Type Mapping)
   <table_constraint> =
           [CONSTRAINT <constraint_name>] PRIMARY KEY (<column_name>
           [, <column_name>]...)

2. Pass the string to the ExecuteUpdate method of the tibdgSession object.
   For example:

   CREATE TABLE mytable (col1 INT PRIMARY KEY, col2 VARCHAR) 
   CREATE TABLE IF NOT EXISTS mytable (col1 INT, col2 VARCHAR CONSTRAINT col2_pk PRIMARY KEY)
   CREATE TABLE table2 (col1 INT PRIMARY KEY, col2 VARCHAR) row_counts=exact

   Note the following points when using the CREATE TABLE command:

   • Only columns with data types which map to ActiveSpaces long and string data types can be primary key columns. For more information, see SQL Data Type Mapping.
   • Specifying a PRIMARY KEY column constraint and a PRIMARY KEY table constraint causes an error.
   • Specifying multiple columns with a PRIMARY KEY constraint causes an error. Use the PRIMARY KEY table constraint instead.
   • Specifying NOT NULL for primary key columns is optional. Primary key columns are implied to be NOT NULL.
   • Specifying NULL for primary key columns causes an error.
   • For non-primary key columns, specifying NOT NULL causes an error. ActiveSpaces treats all non-primary key columns as nullable.
   • Specifying a length for string columns is ignored (for example, VARCHAR(255)). ActiveSpaces does not support limiting the length of string columns.
   • Escaped names are not supported.
     Note: In SQL, an escaped name is a sequence of one or more characters enclosed within SQL escape characters. Trailing spaces are insignificant. An escaped name is case sensitive and special characters used within the escaped name must themselves be escaped.
   • Object names (for example, <column_name>, <index_name>, and <table_name>) are case insensitive. ActiveSpaces converts all object names to lowercase prior to executing the command.