Creating a New Table

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

Before you begin

If permissions are enabled on a table, you must have the tibdg-ddl role to create or modify the table. For more information about roles, see ActiveSpaces Custom Roles. For more information about table permissions, see Grid and Table Permissions.

    Procedure
  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, string, and datetime 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.
    • Object names (for example, <column_name>, <index_name>, and <table_name>) are case insensitive. ActiveSpaces converts all object names to lowercase before executing the command.
    • The property names that can be specified are the same as those used when defining a table by using the administration tool. For example, to enable statistics for a table, use row_counts=exact.