SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
2-35
Examples of ALTER TABLE

Create a new table with the same ANSI name and layout. When an application
accesses the table, the executor determines that the redefinition timestamp is
changed but the ANSI name and layout of the table remains the same. Therefore,
the similarity check passes.

In NonStop SQL/MX, when an application uses the “prototyping” mechanism, it can
specify the new ANSI name at runtime instead of the original ANSI name. The
redefinition timestamp of the table changes but it remains as the same physical
table with a different ANSI name. Therefore, the similarity check passes.
Examples of ALTER TABLE

This example adds a UNIQUE table constraint:
ALTER TABLE persnl.project
ADD CONSTRAINT projtimestamp_uc
UNIQUE (projcode, ship_timestamp);

This example drops a constraint:
ALTER TABLE persnl.project DROP CONSTRAINT projtimestamp_uc;

This example adds a column with a foreign key constraint:
ALTER TABLE persnl.project
ADD COLUMN projlead
NUMERIC (4) UNSIGNED
HEADING 'Project/Lead'
CONSTRAINT projlead_fk REFERENCES persnl.employee;

This example adds a foreign key table constraint. Note that if the foreign key is one
column, you can include the constraint with the column definition, as in the
preceding example.
ALTER TABLE persnl.project
ADD CONSTRAINT projlead_fk
FOREIGN KEY (projlead_fk) REFERENCES persnl.employee;

This example changes a table to control the maximum disk space to be allocated:
ALTER TABLE persnl.employee ATTRIBUTE MAXEXTENTS 300;

This example shows the steps to drop a foreign key. Suppose you have created
two tables, STAFF_M and PROJ_M, and have added foreign key PRI_WK to
STAFF_M:
CREATE TABLE STAFF_M
(EMPNUM CHAR(3) NOT NULL,
EMPNAME CHAR(20),
GRADE DECIMAL(4),
CITY CHAR(15),
PRI_WK CHAR(3),
UNIQUE (EMPNUM));
CREATE TABLE PROJ_M
(PNUM CHAR(3) NOT NULL,
PNAME CHAR(20),
PTYPE CHAR(6),