Create Table

The Create Table statement creates a new table. You can create either a remote or local table. A remote table is a Salesforce object and is exposed in the SFORCE schema. Creating a table in the SFORCE schema creates a remote table. A local table is maintained by the driver and is local to the machine on which the driver is running. A local table is exposed in the PUBLIC schema. Creating a table in the PUBLIC schema creates a local table.

NOTE: Creating tables in Salesforce is not a quick operation. It can take several minutes for Salesforce to create the table and its relationships.

For information on...	See...
Creating a remote table	“Creating a Remote Table”
Creating a local table	“Creating a Local Table”

Creating a Remote Table

Grammar

CREATE TABLE table_name (column_definition [, ...]

[, constraint_definition...])

where:

table_name specifies the name of the new remote table. The table name can be qualified by a schema name using the format schema.table. If the schema is not specified, the table is created in the current schema. See “Alter Session (EXT)” for information about changing the current schema.

column_definition specifies the definition of a column in the new table. See “Column Definition for Remote Tables” for a complete explanation.

constraint_definition specifies constraints on the columns of the new table. See “Constraint Definition for Remote Tables” for a complete explanation.

Column Definition for Remote Tables

Grammar

column_name Datatype [(precision[,scale])...]

[DEFAULT default_value][[NOT]NULL][EXT_ID][PRIMARY KEY]
[START WITH starting_value]

where:

column_name is the name to be assigned to the column.

Datatype is the data type of the column to be created. See “Data Types” in Chapter 21 of the DataDirect Connect Series for ODBC User’s Guide for a list of supported Salesforce data types. You cannot specify ANYTYPE, BINARY, COMBOBOX, ENCRYPTEDTEXT, or TIME data types in the column definition of Create Table statements.

precision is the total number of digits for DECIMAL columns, the number of seconds for DATETIME columns, and the length of HTML, LONGTEXTAREA, and TEXT columns.

scale is the number of digits to the right of the decimal point for DECIMAL columns.

default_value is the default value to be assigned to the column. The following default values are allowed in column definitions for remote tables:

■	For character columns, a single-quoted string or NULL.

■	For datetime columns, a single-quoted Date, Time, or Timestamp value or NULL. You can also use the following datetime SQL functions: CURRENT_DATE, CURRENT_ TIMESTAMP, TODAY, or NOW.

■	For boolean columns, the literals FALSE, TRUE, NULL.

■	For numeric columns, any valid number or NULL.

starting_value is the starting value for the Identity column. The default start value is 0.

[NOT]NULL is used to specify whether NULL values are allowed or not allowed in a column. If NOT NULL is specified, all rows in the table must have a column value. If NULL is specified or if neither NULL or NOT NULL is specified, NULL values are allowed in the column.

EXT_ID is used to specify that the column is an external ID column.

PRIMARY KEY can only be specified when the data type of the column is ID. ID columns are always the primary key column for Salesforce.

START WITH specifies the sequence of numbers generated for the Identity column. It can only be used when the data type of the column definition is AUTONUMBER.

Examples

Example A

Assuming the current schema is SFORCE, the remote table Test is created in the SFORCE schema. The id column has a starting value of 1000.

CREATE TABLE Test (id AUTONUMBER START WITH 1000, Name TEXT(30))

Example B

The table name is qualified with a schema name that is not the current schema, creating the Test table in the SFORCE schema. The table is created with the following columns: id, Name, and Status. The Status column contains a default value of ACTIVE.

CREATE TABLE SFORCE.Test (id NUMBER(9, 0), Name TEXT(30), Status TEXT(10)

DEFAULT 'ACTIVE')

Example C

Assuming the current schema is SFORCE, the remote table dept is created with the name and deptId columns. The deptId column can be used as an external ID column.

CREATE TABLE dept (name TEXT(30), deptId NUMBER(9, 0) EXT_ID)

Constraint Definition for Remote Tables

Grammar

[CONSTRAINT [constraint_name]

{foreign_key_constraint}]

where:

constraint_name is ignored. The driver uses the Salesforce relationship naming convention to generate the constraint name.

foreign_key_constraint defines a link between related tables. See “Foreign Key Clause” for syntax.

A column defined as a foreign key in one table references a primary key in the related table. Only values that are valid in the primary key are valid in the foreign key. The following example is valid because the foreign key values of the dept id column in the EMP table match those of the id column in the referenced table DEPT:

Referenced Table			Main Table
DEPT			EMP
					(Foreign Key)
id	name		id	name	dept id
1	Dev		1	Mark	1
2	Finance		1	Jim	3
3	Sales		1	Mike	2

The following example, however, is not valid. The value 4 in the dept id column does not match any value in the referenced id column of the DEPT table.

Referenced Table			Main Table
DEPT			EMP
					(Foreign Key)
id	name		id	name	dept id
1	Dev		1	Mark	1
2	Finance		1	Jim	3
3	Sales		1	Mike	4

Foreign Key Clause

Grammar

FOREIGN KEY (fcolumn_name)

REFERENCES ref_table (pcolumn_name)

where:

fcolumn_name specifies the foreign key column to which the constraint is applied. The data type of this column must be the same as the data type of the column it references.

ref_table specifies the table to which the foreign key refers.

pcolumn_name specifies the primary key column in the referenced table. For Salesforce, the primary key column is always the rowId column.

Example

Example A

Assuming the current schema is SFORCE, the remote table emp is created with the name, empId, and deptId columns. The table contains a foreign key constraint on the deptId column, referencing the rowId in the dept table created in “Example C”. For the operation to succeed, the data type of the deptId column must be the same as that of the rowId column.

CREATE TABLE emp (name TEXT(30), empId NUMBER(9, 0) EXT_ID, deptId TEXT(18), FOREIGN KEY(deptId) REFERENCES dept(rowId))

Creating a Local Table

Grammar

CREATE [{MEMORY | DISK | [GLOBAL] {TEMPORARY | TEMP}]

TABLE table_name (column_definition [, ...]

[, constraint_definition...])

[ON COMMIT {DELETE | PRESERVE} ROWS]

where:

MEMORY creates the new table in memory. The data for a memory table is held entirely in memory for the duration of the database session. When the database is closed, the data for the memory table is persisted to disk.

DISK creates the new table on disk. A disk table caches a portion of its data in memory and the remaining data on disk.

TEMPORARY and TEMP are equivalent and create the new table as a global temporary table. The GLOBAL qualifier is optional. The definition of a global temporary table is visible to all connections. The data written to a global temporary table is visible only to the connection used to write the data.

NOTE: If MEMORY, DISK, or TEMPORARY/TEMP is not specified, the new table is created in memory.

table_name specifies the name of the new table.

column_definition specifies the definition of a column in the new table. See “Column Definition for Local Tables” for a complete explanation.

constraint_definition specifies constraints on the columns of the new table. See “Constraint Definition for Local Tables” for a complete explanation.

ON COMMIT PRESERVE ROWS preserves row values in a temporary table while the connection is open; this is the default action. ON COMMIT DELETE ROWS empties row values on each commit or rollback.

 

Column Definition for Local Tables

Grammar

column_name Datatype [(precision[,scale])]

[{DEFAULT default_value | GENERATED BY DEFAULT AS IDENTITY

(START WITH n[, INCREMENT BY m])}] | [[NOT] NULL]

[IDENTITY] [PRIMARY KEY]

where:

column_name is the name to be assigned to the column.

Datatype is the data type of the column to be created. See “Data Types” in the DataDirect Connect Series for ODBC User’s Guide for a list of supported Salesforce data types. You cannot specify ANYTYPE, BINARY, COMBOBOX, or TIME data types in the column definition of Create Table statements.

precision is the number characters for CHAR and VARCHAR columns, the number of bytes for BINARY and VARBINARY columns, and the total number of digits for DECIMAL columns.

scale is the number of digits to the right of the decimal point for DECIMAL columns and the number of seconds for DATETIME columns.

default_value is the default value to be assigned to the column. The following default values are allowed in column definitions for local tables:

■	For character columns, a single-quoted string or NULL. The only SQL function that can be used is CURRENT_USER.

■	For datetime columns, a single-quoted Date, Time, or Timestamp value or NULL. You can also use the following datetime SQL functions: CURRENT_DATE, CURRENT_TIME, CURRENT_ TIMESTAMP, TODAY, or NOW.

■	For boolean columns, the literals FALSE, TRUE, NULL.

■	For numeric columns, any valid number or NULL.

■	For binary columns, any valid hexadecimal string or NULL.

The IDENTITY and GENERATED BY DEFAULT AS IDENTITY clauses define an auto-increment column. Either clause can be specified only on INTEGER or BIGINT columns. Identity columns are considered primary key columns, so a table can have only one Identity column.

The GENERATED BY DEFAULT AS IDENTITY clause is the standard SQL syntax for specifying an Identity column.

The START WITH and INCREMENT BY clauses specify the sequence of numbers generated for the Identity column. n and m are the starting and incrementing values, respectively, for an Identity column. The default start value is 0 and the default increment value is 1.

The IDENTITY operator is equivalent to GENERATED BY DEFAULT AS IDENTITY without the optional START WITH clause.

Examples

Example A

Assuming the current schema is PUBLIC, a local table is created. id is an identity column with a starting value of 0 and an increment value of 1 because no Start With and Increment By clauses are specified.

CREATE TABLE Test (id INTEGER GENERATED BY DEFAULT AS

IDENTITY, name VARCHAR(30))

This example is equivalent to the previous example.

CREATE TABLE Test (id INTEGER IDENTITY, name VARCHAR(30))

Example B

Assuming the current schema is PUBLIC, a local table is created. id is an identity column with a starting value of 2 and an increment of 2.

CREATE TABLE Test (id INTEGER GENERATED BY DEFAULT AS

IDENTITY (START WITH 2, INCREMENT BY 2), name VARCHAR(30))

Constraint Definition for Local Tables

Grammar

[CONSTRAINT [constraint_name]

{unique_constraint |

primary_key_constraint |

foreign_key_constraint}]

NOTE: You must specify at least one constraint.

where:

constraint_name specifies a name for the constraint.

unique_constraint specifies a constraint on a single column in the table. See “Unique Clause” for syntax.

Values in the constrained column cannot be repeated, except in the case of null values. For example:

ColA

1

2

NULL

4

5

NULL

A single table can have multiple columns with unique constraints.

primary_key_constraint specifies a constraint on one or more columns in the table. See “Primary Key Clause” for syntax.

Values in a single column primary key column must be unique. Values across multiple constrained columns cannot be repeated, but values within a column can be repeated. Null values are not allowed. For example:

Col A	Col B
2	1
3	1
4	2
5	2
6	2

Only one primary key constraint is allowed in the table.

foreign_key_constraint defines a link between related tables. See “Foreign Key Clause” for syntax.

A column defined as a foreign key in one table references a primary key in the related table. Only values that are valid in the primary key are valid in the foreign key. The following example is valid because the foreign key values of the dept id column in the EMP table match those of the id column in the referenced table DEPT:

Referenced Table			Main Table
DEPT			EMP
					(Foreign Key)
id	name		id	name	dept id
1	Dev		1	Mark	1
2	Finance		1	Jim	3
3	Sales		1	Mike	2

The following example, however, is not valid. The value 4 in the dept id column does not match any value in the referenced id column of the DEPT table.

Referenced Table			Main Table
DEPT			EMP
					(Foreign Key)
id	name		id	name	dept id
1	Dev		1	Mark	1
2	Finance		1	Jim	3
3	Sales		1	Mike	4

Unique Clause

Grammar

UNIQUE (column_name [,column_name...])

where column_name specifies the column to which the constraint is applied. Multiple columns names must be separated by commas.

Primary Key Clause

Grammar

PRIMARY KEY (column_name [,column_name...])

where column_name specifies the primary key column to which the constraint is applied. Multiple column names must be separated by commas.

Foreign Key Clause

Grammar

FOREIGN KEY (fcolumn_name [,fcolumn_name...])

REFERENCES ref_table (pcolumn_name [,pcolumn_name...])

[ON {DELETE | UPDATE}

{CASCADE | SET DEFAULT | SET NULL}]

where:

fcolumn_name specifies the foreign key column to which the constraint is applied. Multiple column names must be separated by commas.

ref_table specifies the table to which a foreign key refers.

pcolumn_name specifies the primary key column or columns referenced in the referenced table. Multiple column names must be separated by commas.

ON DELETE is a clause that defines the operation performed when a row in the table referenced by a foreign key constraint is deleted. One of the following operators must be specified in the On Delete clause:

■	CASCADE specifies that all rows in the foreign key table that reference the deleted row in the primary key table are also deleted.

■	SET DEFAULT specifies that the value of the foreign key column is set to the column default value for all rows in the foreign key table that reference the deleted row in the primary key table.

■	SET NULL specifies that the value of the foreign key column is set to NULL for all rows in the foreign key table that reference the deleted row in the primary key table.

ON UPDATE is a clause that defines the operation performed when the primary key of a row in the table referenced by a foreign key constraint is updated. One of the following operators must be specified in the On Update clause:

■	CASCADE specifies that the value of the foreign key column for all rows in the foreign key table that reference the row in the primary key table that had the primary key updated are updated with the new primary key value.

■	SET DEFAULT specifies that the value of the foreign key column is set to the column default value for all rows in the foreign key table that reference the row that had the primary key updated in the primary key table.

■	SET NULL specifies that the value of the foreign key column is set to NULL for all rows in the foreign key table that reference the row that had the primary key updated in the primary key table.

Both the On Delete and On Update clauses can be used in a single foreign key definition.

Example

Example A

Assuming the current schema is PUBLIC, the emp table is created with the name, empId, and deptId columns. The table contains a foreign key constraint on the deptId column that references the id column in the dept table. In addition, it sets the value of any rows in the deptId column to NULL that point to a deleted row in the referenced dept table.

CREATE TABLE emp (name VARCHAR(30), empId INTEGER, deptId INTEGER, FOREIGN KEY(deptId) REFERENCES dept(id)) ON DELETE SET NULL)