Supported only for Salesforce-based data stores. Defines a table column.
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 Supported data types for a list of supported 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 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
In the following example, the table name is qualified with the schema name, which will create the Test table in the SFORCEschema. 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 B
In the current schema, the following example creates a Test table and gives the id column a starting value of 1000.
CREATE TABLE Test (id AUTONUMBER START WITH 1000, Name TEXT(30))
Example C
The following example creates a dept table with name and deptId columns in the current schema. The deptId column can be used as an external ID column.
For character columns, a single-quoted string or NULL.