How field changes affect database tables

When you restructure a regular form by adding, deleting, or changing the length of fields, AR System restructures the underlying database to reflect those changes.

For join forms, adding or deleting fields simply adds or removes the references to the fields in the underlying form. You cannot change the length of a field in a join form because it is defined in the underlying form.

For view forms, the database view is re-created when any fields are added or removed. The database is not re-created if field properties (for example, length) are changed.

Table updates when adding fields to a form

When you add a field to a form, a column is added to the main data table by using the ALTER TABLE command. The structure of the database is changed to add the column according to the rules stated in Relationship-between-forms-and-database-tables. 

The value for the new field in existing entries is NULL even if it is a required field. You can change these values at any time. When the field is added, it can be used for all existing or future entries. Use the BMC User Modify All operation to assign a default value for the field.

Table updates when deleting fields from a form

Deleting a field from a form removes the corresponding column and all data associated with the field from the database. The following sections describe how each database deletes fields.

Any indexes that are defined as part of the form definition are re-created on the rebuilt table.

In Oracle and Microsoft SQL Server databases, the ALTER TABLE ... DROP ... syntax is used to remove the column from the table.

Table updates for Microsoft SQL Server when changing character fields

In Microsoft SQL Server databases, if the field is created in AR System 5.1 and later, the length of a character field is changed in one of these ways:

• If the original size is <= 8000 bytes and you decrease the length, no change is made to the table.
• If the original size is > 8000 bytes and the new length is > 8000 bytes, no change is made to the table.
• For any other change in length, a column is created with the new length restriction. All data from the original column is copied to the new column, and the original column is deleted from the main table.

If the field is created in a version of AR System earlier than 5.1, the length of a character field is changed in one of these ways:

• If the original size is <= 255 bytes and the new length is <=8000 bytes, no change is made to the table.
• If the original size is > 255 bytes and the new length is > 8000 bytes, no change is made to the table.
• For any other change in length, a column is created with the new length restriction. All data from the original column is copied to the new column, and the original column is deleted from the main data table.

In Microsoft SQL Server 2005, when the underlying database table is marked for database replication, you cannot change the field length. If you try to do so, the ALTER TABLE command returns this error: Cannot rename the table because it is published for replication. (SQL Server 15051). To resolve this, turn off database replication, change the field size, and then turn database replication on. For more information, see the Microsoft SQL Server documentation.

Table updates for Oracle when changing character fields

The following table shows the changes that AR System makes to Oracle databases when you change the length of character fields. The way that field length changes are handled depends on the initial size of the field and whether the field was created in the current version or a previous version of  AR System.

Server actions when changing full text indexed fields

If the length of a full text indexed field is changed, the full text index might be restructured. The following table describes the server actions that can occur.

 The following warning appears after the length change is saved: A rebuilding of the corresponding full text index has been initiated due to the field length change (ARWARN 681).