Open topic with navigation

TIBCO ActiveSpaces® - Enterprise Edition 5.0.0

# Creating a New Table

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

Before you begin

If the 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 that 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 running 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`.

Did you find this helpful?

Yes No
Great!
Thanks for taking the time to give us some feedback.
Sorry about that

Why wasn't this helpful? (check all that apply)

Could not find what I was looking for
Instructions confusing or unclear
Instructions did not work

Want to tell us more?

Please select at least one option or provide details about the issue!!

Error occurred while submitting feedback.

Error2 occurred while submitting feedback.

TIBCO ActiveSpaces® - Enterprise Edition

Copyright © 2009-2025. Cloud Software Group, Inc. All Rights Reserved.