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

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—523723-004
7-37
Guidelines for Creating Constraints
When working with large tables, you should create the supporting index for each table
before you create the constraint. You precreate the index by issuing a CREATE
[UNIQUE] INDEX on the columns you want to specify in the constraint. UNIQUE is
required for the PRIMARY KEY and UNIQUE constraints. Then you issue the ALTER
TABLE ADD CONSTRAINT statement.
When you precreate the index, you can specify the particular attributes you want for
the index (for example, index size, number and location of partitions, and index
location) instead of relying on possibly inadequate default values of an index created
as a by-product of creating a table constraint.
For more information about nonunique indexes, see Precreating Indexes or Managing
Constraint-Created Indexes on page 4-13 and Creating Indexes for SQL/MX Tables on
page 7-31.
Guidelines for Creating Constraints
•
Aggregate functions and subqueries are not allowed in the search condition
defining a CHECK constraint.
•
To make the best use of constraints that are implemented by indexes, create your
indexes before you create your constraints.
•
Use the NOT DROPPABLE option on a NOT NULL constraint to reduce the space
required for the table. In a column that allows nulls—or that might allow nulls at a
later time—extra bytes are added in each row to store the null indicator. If you
specify that the NOT NULL constraint is NOT DROPPABLE, NonStop SQL/MX
creates the table without these extra bytes.
Examples for Creating Constraints for SQL/MX Tables
To add a UNIQUE table constraint to an existing table:
ALTER TABLE persnl.project
ADD CONSTRAINT projtimestamp_uc
UNIQUE (projcode, ship_timestamp);
To add a FOREIGN KEY constraint to an existing table:
ALTER TABLE persnl.project
ADD CONSTRAINT projlead_fk
FOREIGN KEY (projlead) REFERENCES persnl.employee;
Creating Views of SQL/MX Tables
SQL/MX views comply with the SQL:1999 standard. The SQL/MP notions of shorthand
and protection views do not apply to SQL/MX views.
To create an ANSI view, use the CREATE VIEW statement. The columns specified for
a view can be from one SQL/MX table or—through the use of UNIONS, JOINS, or