• You are here:
• Home
• >
• Firebird resources
• >
• Documentation
• >
• Function & Design
• >
• Document 150

Alter Column Definitions - Functional Specification

Description

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

ALTER DOMAIN name {

	  [SET DEFAULT {literal | NULL | USER}]

	| [DROP DEFAULT]

	| [ADD {CONSTRAINT] CHECK ( dom_search_condition )]

	| [DROP CONSTRAINT]

	| 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

ALTER TABLE table1 ALTER [COLUMN] field1 POSITION 4;

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)

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).

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

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.