SQL/MP Installation and Management Guide

Adding, Altering, Removing, and Renaming
Database Objects
HP NonStop SQL/MP Installation and Management Guide—523353-004
7-12
Adding Constraints
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 or if you want
to add comments on the new column, you can run the CREATE CONSTRAINT or
COMMENT statement at any time after the column is added.
For additional information about column positioning and performance-related aspects
of columns, see Defining Columns on page 5-19.
Adding Constraints
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. To
add a constraint, use the CREATE CONSTRAINT statement.
Adding a constraint to a table invalidates any programs that depend on the table. You
should include steps to explicitly SQL compile the dependent programs to avoid
automatic recompilation and to return the application to a valid state.
To add a constraint, follow these steps:
1. Start an SQLCI session. Enter a LOG command to initiate a log file for the
statements and commands entered in this session. Keep the log for your records.
2. Determine the name of the table for which you want to add the constraint.
3. Check any existing constraints by querying the CONSTRNT catalog table and
determine whether the new constraint would supersede or conflict with any
constraint already defined for the same table.
4. Enter a query on the table by making the predicate of the query the negation of the
constraint. This query would identify rows that do not satisfy the constraint. If the
query identifies any rows, change or delete the rows before creating the constraint.
If the query refers to a collation, verify that the collation exists and is secured to
allow you write access before creating the constraint.
5. Determine which programs depend on the table by using the DISPLAY USE OF
command. These programs will be invalidated.
6. Optionally, prevent the use of the table for the duration of the CREATE
CONSTRAINT operation to eliminate conflicts in table access; this operation
requires exclusive use of the table.
7. Enter the CREATE CONSTRAINT statement.
8. SQL compile the invalidated programs.
9. Restart use of the table if you stopped its use.
For additional information on constraints and related performance issues, see Creating
Constraints on Data on page 5-51 and Checking Data Integrity on page 14-23.