Synchronizing Record Definitions With Tables

Evaluating Modifications to Field and Record DefinitionsIf you change either a field definition or a record definition, you might have to alter the associated SQL table to synchronize it, depending on what the changes are. Use the following table to determine if changes to the table are required. 

Category	Table Structure Changes (Requires Create or Alter Table)	Index Changes (Requires Create Index)	Online System Changes (No database changes required)
Field properties	Field name Length Type Delete/Add field from record definition	N/A	Format Long name Short name Translate table values
Record field use properties	N/A	Key (new) Alternate search key Key direction (ascending or descending) Duplicate order key	Search key (if already a key) List box item Defaults (value and page control) Auto update Field audits
Record field edits properties	Value required (Long Character, Date, Time, DateTime, Image)	N/A	Prompt table edit Translate table edit Yes/No edit Value required (Numeric, Character) Reasonable date check
Record field PeopleCode	N/A	N/A	PeopleCode

If you change the table structure, you must either build or alter the table. The Alter Table process is similar to the Create Table process, except that it does not drop existing application data tables and the data that they contain. 

Drop column if data present	Select to drop the column and data and write a warning to the build log. You are prompted at runtime to confirm this option if there is potential data loss.
Skip record if data present	Select to skip the record with data and write an error message to the log. Processing continues with the next record.
Truncate data if field too short	Select to alter the record and write a warning message to the build log. You are prompted at runtime to confirm this option if there is potential data loss.
Skip record if field too short	Select to skip the record with excess data and write an error message to the build log. Processing continues with the next record.
Adds, Changes, Renames, and Deletes	Select all check boxes so that record definition changes are passed to the altered table. Clear a box if you don’t want a change in the altered table (a deleted column, for example).
Alter even if no changes	When fields are rearranged within a record definition and no other properties are changed, some database platforms do not pick up the new field order as a change. To synchronize the new order in the table, select this option.

Alter Table Options

There are two options for altering tables: Alter in Place and Alter by Table Rename.  Normally you accept the default value assigned. 

Alter in Place	Performs the SQL Alter command to change the structure of an application table. New columns (if any) are placed at the end of the table.
Alter by Table Rename	Creates a temporary table based on the record definition, copies data from the original table, drops the original table, and renames the temporary table with the original name.

If new fields are being added to the record definition, and field order is important, you select the Alter by Table Rename method.