SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

To integrate the new column into the existing database or application programs:
• If you want to create a new index using the new column, follow the steps for “Adding Indexes
to SQL/MX Tables” (page 155).
• If you want to add the new column to an existing index, first follow the steps for “Dropping
SQL/MX Indexes” (page 172), then follow the steps for “Adding Indexes to SQL/MX Tables”
(page 155) to add the new column definition. You cannot alter an index or view to add a
column.
• If you want to create a new view using the new column, follow the steps for “Adding Views”
(page 161).
• If you want to add the new column to an existing view, first follow the steps for “Dropping
Views” (page 177), then follow the steps for “Adding Views” (page 161). You cannot alter a
view to add a column.
• If you want to use the new column in programs, you must change existing programs to refer
to the new column. You might need to change screen sections to display the column on the
screen, and you might need to change code sections to retrieve or update the column. After
you SQL compile a program changed to use the new table definition, the program can use
the column.
• If you want to add constraints that control values in the new column, execute the ALTER TABLE
ADD CONSTRAINT statement at any time after the column is added.
Example of Adding a Column to a Table
The following example adds a column to the CUSTOMER table:
>> LOG myfile;
>> ALTER TABLE CAT.SCH.CUSTOMER
+> ADD COLUMN PRIOR_YEARS_SALES
+> PIC S9(9)V99 COMP DEFAULT CURRENTTIME;
--- SQL operation complete.
Adding Constraints
To add any of these constraints to a table, use the ALTER TABLE ADD CONSTRAINT statement:
• CHECK
• PRIMARY KEY
• REFERENTIAL INTEGRITY (RI)
• UNIQUE
Adding a constraint to the database is similar to making a program change: any future data
insertions or updates must satisfy the new rule imposed by the constraint. In addition, all existing
rows must satisfy the rule before a constraint can be added.
For more information, see the SQL/MX Reference Manual.
Steps for Adding a Constraint
1. Start an MXCI session. Enter a LOG command to initiate a log file for statements and commands
entered in this session. Keep the log for your records.
2. Determine the name of the table to which you want to add the constraint.
3. Query the OBJECTS table to ensure the new constraint name is unique among existing constraint
names in the schema.
4. Query the table data to ensure it satisfies the rule imposed by the constraint. (For RI constraints,
also ensure the data in the column(s) in the referenced table satisfy the constraint.) If you are
creating a unique index, query the table data to ensure no duplicate entries are present.
154 Adding, Altering, and Dropping SQL/MX Database Objects