Alter Table

The Alter Table statement adds a column, removes a column, or redefines a column in a table. The table being altered can be either a remote or local table. A remote table is a Salesforce object and is exposed in the SFORCE schema. 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.

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

Altering a Remote Table

Grammar

ALTER TABLE table_name

[add_clause]

[drop_clause]

where:

table_name specifies an existing remote table.

add_clause specifies a column or a foreign key constraint to be added to the table. See “Add Clause: Columns” and “Add Clause: Constraints” for a complete explanation.

drop_clause specifies a column to be dropped from the table. See “Drop Clause: Columns” for a complete explanation.

NOTE: You cannot drop a constraint from a remote table.

Add Clause: Columns

Use the Add clause to add a column to an existing table. It is optional.

Grammar

ADD [COLUMN] column_name Datatype ...

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

This clause adds a column to the table. It defines a column with the same syntax as the Create Table command (see “Column Definition for Remote Tables”). If NOT NULL is specified and the table is not empty, a default value must be specified. In all other respects, this command is the equivalent of a column definition in a Create Table statement.

default_value is the default value to be assigned to the column. See “Column Definition for Remote Tables” for details.

You cannot specify ANYTYPE, BINARY, COMBOBOX, or TIME data types in the column definition of Alter Table statements.

If a SQL view includes SELECT * FROM for the table to which the column was added in the view’s Select statement, the new column is added to the view.

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

Examples

Example A

Assuming the current schema is SFORCE, this example adds the status column with a default value of ACTIVE to the test table.

ALTER TABLE test ADD COLUMN status TEXT(30) DEFAULT 'ACTIVE'

Example B

Assuming the current schema is SFORCE, this example adds a deptId column that can be used as a foreign key column.

ALTER TABLE test ADD COLUMN deptId TEXT(18)

Add Clause: Constraints

Use the Add clause to add a constraint to an existing table. It is optional.

Grammar

ADD [CONSTRAINT constraint_name] ...

This command adds a constraint using the same syntax as the Create Table command (see “Constraint Definition for Remote Tables”).

NOTES:

■	The only type of constraint you can add is a foreign key constraint.

■	When adding a foreign key constraint, the table that contains the foreign key must be empty.

Example A

Assuming the current schema is SFORCE, a foreign key constraint is added to the deptId column of the test table, referencing the rowId of the dept table. For the operation to succeed, the dept table must be empty.

ALTER TABLE test ADD FOREIGN KEY (deptId) REFERENCES dept(rowId)

Drop Clause: Columns

Use the Drop clause to drop a column from an existing table. It is optional.

Grammar

DROP {[COLUMN] column_name}

where column_name specifies an existing column in an existing table.

NOTES:

■	The column being dropped cannot have a constraint defined on it.

■	Drop fails if a SQL view includes the column.

Example A

This example drops the status column. For the operation to succeed, the status column cannot have a constraint defined on it and cannot be used in a SQL view.

ALTER TABLE test DROP COLUMN status

Altering a Local Table

Grammar

ALTER TABLE table_name

[add_clause]

[drop_clause]

[rename_clause]

where:

table_name specifies an existing local table.

add_clause specifies a column or constraint to be added to the table. See “Add Clause: Columns” and “Add Clause: Constraints” for a complete explanation.

drop_clause specifies a column or constraint to be dropped from the table. See “Drop Clause: Columns” and “Drop Clause: Constraints” for a complete explanation.

rename_clause specifies a new name for the table. See “Rename Clause” for a complete explanation.

 

Add Clause: Columns

Use the Add clause to add a column to an existing table. It is optional.

Grammar

ADD [COLUMN] column_name Datatype ...

[BEFORE existing_column]

This clause adds a column to the end of the column list. It defines a column with the same syntax as the Create Table command (see “Column Definition for Local Tables”). If NOT NULL is specified and the table is not empty, a default value must be specified. In all other respects, this command is the equivalent of a column definition in a Create Table statement.

You cannot specify ANYTYPE, BINARY, COMBOBOX, or TIME data types in the column definition of Alter Table statements.

The optional Before existing_column can be used to specify the name of an existing column so that the new column is inserted in a position just before the existing column.

If a SQL view includes SELECT * FROM for the table to which the column was added in the view’s Select statement, the new column is added to the view.

Examples

Example A

Assuming the current schema is PUBLIC, this example adds the status column with a default value of ACTIVE to the test table.

ALTER TABLE test ADD COLUMN status VARCHAR(30) DEFAULT 'ACTIVE'

Example B

Assuming the current schema is PUBLIC, this example adds a deptId column that can be used as a foreign key column.

ALTER TABLE test ADD COLUMN deptId VARCHAR(18)

Add Clause: Constraints

Use the Add clause to add a constraint to an existing table. It is optional.

Grammar

ADD [CONSTRAINT constraint_name] ...

This command adds a constraint using the same syntax as the Create Table command (see “Constraint Definition for Local Tables”).

NOTES:

■	You cannot add a Unique constraint if one is already assigned to the same column list. A Unique constraint works only if the values of the columns in the constraint columns list for the existing rows are unique or include a Null value.

■	Adding a foreign key constraint to the table fails if, for each existing row in the referring table, a matching row (with equal values for the column list) is not found in the referenced table.

Example

Example A

Assuming the current schema is PUBLIC, this example adds a foreign key constraint to the deptId column of the test table that references the rowId of the dept table.

ALTER TABLE test ADD CONSTRAINT test_fk FOREIGN KEY (deptId) REFERENCES dept(id)

Drop Clause: Columns

Use the Drop clause to drop a column from an existing table. It is optional.

Grammar

DROP {[COLUMN] column_name}

where column_name specifies an existing column in an existing table.

Drop fails if a SQL view includes the column.

Example

Example A

This example drops the status column. For the operation to succeed, the status column cannot have a constraint defined on it and cannot be used in a SQL view.

ALTER TABLE test DROP COLUMN status

Drop Clause: Constraints

Use the Drop clause to drop a constraint from an existing table. It is optional.

Grammar

DROP {[CONSTRAINT] constraint_name}

where:

constraint_name specifies an existing constraint.

NOTE: The specified constraint cannot be a primary key constraint or unique constraint.

Example

Example A

This example drops the test_fk constraint.

ALTER TABLE test DROP CONSTRAINT test_fk

Rename Clause

Use the Rename clause to rename an existing table. It is optional.

Grammar

RENAME TO new_name

where new_name specifies the new name for the table.

Example

Example A

This example renames the table to test2.

ALTER TABLE test RENAME TO test2