Alter Column Definitions - Functional Specification


The ability to alter domain and column definitions is unique to InterBase. This ability, while not part of standard SQL, is supported by GDML and is surfaced by both QLI and GDEF (both of which are now no longer supported as part of the InterBase product). This feature has been requested by customers and can be useful as a migration tool.

The current scope of this project for the upcoming 6.0 release is to allow only the following changes to be made to field or domain definitions:

  • Changing the data type of either a field or a domain
  • Changing the name of either a field or a domain
  • Changing the position of a field with respect to the other fields

The GDML implementation allows for the following:

modify field field_name <field_attributes> [system_flag integer]

This statement would all the user to change the characteristics of a global field definition (domain) such as datatype, comments, edit-string, constraints, etc. It would not allow the user to change the name of the global field.

modify relation x modify field field_name <field_attributes>

This statement allows the user to change relation-specific features of the field. The user is only allowed to change the field's

  • position
  • description
  • edit string
  • query header
  • query name
  • security class
  • parent (i.e. what it is based on)
  • system flag

The GDML functionality above consists of simple queries which modify the system relations to acheive the desired effect. It is the current goal of the project to duplicate this behavior in the same way via SQL if possible.

This feature will only deal with the following for the 6.0 release:

  • Altering domain names and data types
  • Altering column names, data types, and position

Please refer to the requirements / constraints section for additional information on how these changes will be handled.

User Interface/Usability

The proposed SQL syntax is (changes in bold. Only parts of the original syntax is shown):

ALTER TABLE operation [,operation ...];
operation = 

	{  ADD col_def

	 | ADD tconstraint

	 | ALTER [COLUMN} simple_column_name alter_rel_fld

	 | DROP col

	 | DROP CONSTRAINT constraint
alter_rel_fld =   new_col_name

		| new_col_type

		| new_col_pos

	  [SET DEFAULT {literal | NULL | USER}]


	| [ADD {CONSTRAINT] CHECK ( dom_search_condition )]


	| new_col_name

	| TYPE data_type

new_col_name = TO simple_column_name
new_col_type = TYPE datatype_or_domain
new_col_pos = POSITION integer

The operations above are executed serially as with any other SQL statement. For example, if the following statement was issued:

alter table table1 alter field1 position 3 modify field1 position 2

the result would be that field 1 would be in the second position in the table.

To change the name of a column

ALTER TABLE table1 ALTER [COLUMN] field1 TO field2;

To change the datatype of a column

ALTER TABLE table1 ALTER [COLUMN] field1 TYPE char(20);

To change the position of a field


To change the name of a domain

ALTER DOMAIN domain1 TO domain2;

To change the datatype of a domain

ALTER DOMAIN domain1 TYPE char(20);

Error Messages

The following list contains new error messages which may be surfaced when attempting to alter column or field definitions (the text is not final but gives an indication of the types of errors)

Symbol Error Message SQL Code
isc_dyn_dependency_exists Column %s from table %s is referenced in %s -616
isc_dyn_dtype_invalid Cannot change datatype for column %s. Changing datatype is not supported for BLOB or ARRAY columns. -688
isc_dyn_char_fld_too_small New size specified for column %s must be greater than %d characters. -604
isc_dyn_invalid_dtype_conversion Cannot change datatype for column %s. Conversion from base type %s to base type %s is not supported. -604
isc_dyn_dtype_conv_invalid Cannot change datatype for column %s from a character type to a non-character type. -688
isc_dyn_domain_name_exists Cannot rename domain %s to %s. A domain with that name already exists. -612
isc_dyn_field_name_exists Cannot rename column %s to %s. A column with that name already exists in table %s. -612

Requirements and Constraints

  • If there are any dependencies (except for granted privileges and indexes) on a column, then the user will not be allowed to alter its definition. The engine will make an attempt to return the dependencies if possible.
  • Any changes to the field definitions may require indicies to be rebuilt.Conversions from non-character data to character data will be allowed with the following restrictions:
  • Blob and array types are not convertible.
  • Field types can not be shortened (character or numeric data).
  • to hold the existing information.
  • Conversions from character data to non-character data will not be allowed .
  • If there are foreign / primary key dependencies, the engine will attempt to update them .
  • Changing field and domain definitions online. The system tables are updated across all connections once the changes have been made and committed. This can affect operations which are currently using the older field definitions.

Datatype Conversions

The following chart should be used to determine if a datatype conversion is valid (new types are across the top, original types are along the side).

  Blob Char SQL Date Decimal Double Float Integer Numeric Time
Time Small
Char   X                   X
SQL Date    X X           X    
Decimal   X   X       X       X
Double   X     X X           X
Float   X     X X            X
Integer   X   X X   X X       X
Numeric   X           X       X
Timestamp   X X           X X    
Time   X             X X    
Smallint   X   X X X X X     X X
Varchar   X                   X

When converting from numeric types to character types, the character field will have a minimum length .

Data type Minimum Length
Decimal 20
Double 22
Float 13
Integer 11
Numeric 22
Smallint 6

Migration Issues

This feature can be used as a migration tool. Users can will be able to update existing column definitions to remove keyword names and ensure that their numeric data types are correct (in conjunction with the 64-bit integer work) .

Features for Future Consideration

The following list of features are not planned for implementation in the 6.0 release but may be added in future releases.

  • Rename tables, stored procedures, and other database objects.
  • Resetting the computed by, the default, the not null and collate clauses for the field.
  • Modifying column constraints.