SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-55
Creating Collations
+> EMPNUM < 9999;
--- SQL operation complete.
For the constraint VALID_EMPLOYEE_NUMBER, you might create this comment:
>> COMMENT ON CONSTRAINT VALID_EMPLOYEE_NUMBER
+> ON $VOL1.PERSNL.EMPLOYEE
+> IS "VALID EMPLOYEE NUMBERS ARE 1 to 9998";
--- SQL operation complete.
This example creates a constraint to ensure that the delivery date is greater than or
equal to the order date:
>> CREATE CONSTRAINT VALID_DELIV_DATE
+> ON $VOL.SALES.ORDERS
+> CHECK DELIV_DATE >= ORDER_DATE;
--- SQL operation complete.
Creating Collations
A collation is an SQL object that contains rules for:
•
Collating sequence (the sequence in which characters are ordered for sorting)
•
Case (upshifting and downshifting)
•
Character class
Collations can be applied to single-byte character columns in SQL tables.
You can define and create a collation and then associate the collation with a column in
an SQL table. For example, you can define a collation by using the CREATE
COLLATION statement that sorts characters in a different order than their character
codes dictate. (If you do not specify a collation for a column, or if you specify the
COLLATE CHARACTER SET clause, SQL collates the column according to the binary
values of the data.)
Then when you create a column that has a character data type and a single-byte
character set by using a CREATE TABLE or ALTER TABLE statement, you can specify
the name of the collation to associate with the column. If the column is part of the
primary key for the table, the collation also affects the storage order for rows in the
table.
A collation name must be a Guardian name.
SQL allows a collation (or a class MAP DEFINE name that points to a collation) in the
COLLATE clause of these statements:
•
CREATE TABLE, ALTER TABLE...ADD COLUMN, or CREATE VIEW statement to
specify a default collating sequence for one or more columns in a table or view
•
The GROUP BY or ORDER BY clause in a SELECT statement to override the
default collating sequence for a column