skip to main content
Designing and coding the IP : DDL object management : Alter Table processing
 

Alter Table processing

To support Alter Table processing, IP GETSUPPORT must return TRUE for IP_SUPPORT_ALTER_TABLE.
The supported SQL syntax for altering a table is:
ALTER TABLE table_name alter-table-action
alter-table-action = ADD [COLUMN] column-definition
| ADD ( column-definition [,column-definition]...)
| DROP [COLUMN] column-identifier
| ALTER [COLUMN] column-identifier SET DEFAULT default-value
| ALTER [COLUMN] column-identifier DROP DEFAULT
| ADD CONSTRAINT constraint-name UNQIUE (column-identifier)
| DROP CONSTRAINT
column-definition = column-identifier data-type [DEFAULT default-value] [column-constraint [column-constraint]...]
column-constraint = NOT NULL | UNIQUE [KEY]
When a SQL query of this form is issued, OpenAccess SDK SQL engine maps this information to a list of schema objects of types damobj_table, damobj_column, and damobj_stat.
The following table shows how the Alter Table query is mapped to the schema objects.
 
Table 11: ALTER TABLE syntax to schema objects mapping  
Schema object properties
ALTER TABLE syntax
Remarks
damobj_table
table_qualifier
table_owner
table_name
ALTER TABLE [qualifier].[owner].table_name
The table information is mapped to the schema object of type: DAMOBJ_TYPE_TABLE.
damobj_column
column_name = column-identifier
data_type=XO_TYPE_XXX
char_max_length =N (Set for CHAR & VARCHAR fields)
numeric_precision = M (Set for NUMERIC fields)
numeric_scale = N (Set for NUMERIC fields)
nullable = XO_NO_NULLS (If NOT NULL clause is specified. Otherwise set to XO_NULLABLE)
userdata (Set if the USERDATA clause was defined for the column)
userdata = “DEFAULT default-value”
type = SQL_ADD_COLUMN
ADD column-identifier
data-type [NOT NULL]
USERDATA 'literal string'
DEFAULT default-value
The column information (name, data-type, length, precision, scale, nullable, userdata) is mapped to schema object of type DAMOBJ_TYPE_COLUMN.
damobj_column
column_name = column-identifier
type = SQL_DROP_COLUMN
DROP column-identifier
The column information (name) is mapped to schema object of type: DAMOBJ_TYPE_COLUMN.
damobj_column
column_name = column-identifier
userdata = “SET DEFAULT default-value” or “DROP DEFAULT”
type = SQL_ALTER_COLUMN
ALTER [COLUMN] column-identifier {SET DEFAULT default-value | DROP DEFAULT}
The column information (name) is mapped to schema object of type DAMOBJ_TYPE_COLUMN.
damobj_stat
column_name = column name for which the indexing information is specified
index_name = constraint-name
non_unique = FALSE
type = SQL_ADD_CONSTRAINT
ADD CONSTRAINT constraint_name UNIQUE (column-identifier)
The index information is mapped to one schema object of type DAMOBJ_TYPE_STAT.
damobj_stat
index_name = constraint-name
type = SQL_DROP_CONSTRAINT
DROP CONSTRAINT constraint_name
The index information is mapped to one schema object of type DAMOBJ_TYPE_STAT.