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