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. 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.