SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-54
Examples of Creating Constraints
ensure that the rows satisfy the constraint. With this testing technique and the
automatic transactions, the operation minimizes the TMF overhead of a potentially
very long transaction and reduces output to the audit trails.
Examples of Creating Constraints
This example creates a constraint that checks for a valid employee number in the
range 0 through 9999. The INFO DEFINE command displays DEFINEs used to identify
the table for the constraint.
-- DEFINEs were previously added during this SQLCI
-- session or inherited from the command interpreter.
>> INFO DEFINE =EMPLOYEE;
DEFINE NAME =EMPLOYEE
CLASS MAP
FILE \SYS1.$VOL1.PERSNL.EMPLOYEE
>> CREATE CONSTRAINT VALID_EMPLOYEE_NUMBER
+> ON =EMPLOYEE
+> CHECK EMPNUM BETWEEN 0 AND 9999;
This example creates a constraint, VALID_JOB_CODES, that ensures job code values
are between 100 and 900, and the department is greater than or equal to 2500 but less
than 5000. This constraint could apply to a partition of the table.
>> CREATE CONSTRAINT VALID_JOB_CODES
+> ON \SYS1.$VOL1.PERSNL.DEPT
+> CHECK JOBCODE BETWEEN 100 AND 900
+> AND (DEPTNUM >= 2500 AND
+> DEPTNUM < 5000);
This example creates a constraint, VALID_PRICE_RANGE, that checks for a valid
markup range in which the price must be greater than or equal to a 20 percent markup
but less than or equal to a 200 percent markup:
>> CREATE CONSTRAINT VALID_PRICE_RANGE
+> ON $VOL3.INV.PARTLIST
+> CHECK PRICE >= PARTS_COST * 1.20 AND
+> PRICE <= PARTS_COST * 2.00;
This example creates a constraint, MIN_INVENTORY_FACTOR, that ensures that the
quantity ordered is less than or equal to the quantity on hand or that the quantity
ordered can be available by the estimated date when additional parts will be available:
>> CREATE CONSTRAINT MIN_INVENTORY_FACTOR
+> ON $VOL3.INV.INVFILE
+> CHECK QTY_ORDERED <= QTY_ON_HAND OR
+> (QTY_ORDERED <= QTY_ON_HAND + QTY_RECEIVED AND
+> DELIV_DATE > EST_RECEIVED_DATE);
This example creates a constraint to ensure that the value of the EMPNUM column is
greater than 0 and less than 9999:
>> CREATE CONSTRAINT VALID_EMPLOYEE_NUMBER
+> ON $VOL1.PERSNL.EMPLOYEE
+> CHECK EMPNUM > 0 AND