SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Adding, Altering, and Dropping SQL/MX Database
Objects
HP NonStop SQL/MX Installation and Management Guide—523723-004
9-7
Adding Constraints
Example of Adding a Column to a Table
This 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.
5. Use the DISPLAY USE OF command to identify which user modules are
associated with this object. See the similarity check criteria in the SQL/MX
Programming Manual for C and COBOL and the SQL/MX Programming Manual for
Java to determine if your changes are likely to cause similarly check to fail and
force automatic recompilation. If they will, you should SQL compile these modules
after making the changes to avoid expensive automatic recompilations at run time.
SQL applications that are running while you make these changes will still undergo
automatic recompilation.
For information about explicit and automatic recompilation, see the SQL/MX
Programming Manual for C and COBOL and the SQL/MX Programming Manual for