Purpose

Defines a column for remote tables.

Syntax

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 the DataDirect Connect Series for JDBC 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 NUMBER, CURRENCY, and PERCENT columns, and the length of HTML, LONGTEXTAREA, and TEXT columns.
scale
is the number of digits to the right of the decimal point for NUMBER, CURRENCY, and PERCENT 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.

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)