SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

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 subqueries— from several
SQL/MX tables. You cannot use columns from SQL/MP tables.
Use the CREATE VIEW statement to define new column names for a view, instead of using the
column names from the underlying tables. When using the view, applications use the view-defined
column names. Applications can also use views to rename, reorder, and project subsets of columns
from one or more tables.
For more information, see the SQL/MX Reference Manual. For background information about
views, see “Understanding and Planning SQL/MX Tables” (page 29).
For more information about formulating queries before you create views for your SQL/MX database,
see the SQL/MX Query Guide
Rules for Creating and Accessing Views
• A view can have a single underlying table or multiple tables.
• To create a view, you must have SELECT privileges for the underlying table.
• Granting a privilege on a view does not grant that privilege to the corresponding column of
the underlying table.
See “Access Privileges for SQL/MX Database Objects” (page 79) for more information.
Examples for Creating Views for SQL/MX Tables
This example creates a view on a single table without a view column list:
CREATE VIEW SALES.MYVIEW1 AS
SELECT ordernum, qty_ordered FROM SALES.ODETAIL;
This example creates a view with a column list:
CREATE VIEW SALES.MYVIEW2
(v_ordernum, t_partnum) as
SELECT v.ordernum, t.partnum
FROM SALES.MYVIEW1 v, SALES.ODETAIL t;
This example creates a view with a CHECK OPTION:
CREATE VIEW SALES.MYVIEW3
(ordernum HEADING 'Number of Order') AS
SELECT ordernum FROM SALES.ODETAIL
WHERE partnum < 1000 WITH CHECK OPTION;
Creating Views of SQL/MX Tables 101