SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-22
Examples of ALTER TABLE
SQL/MX Extensions to ALTER TABLE
These clauses are SQL/MX extensions:
•
ATTRIBUTES clause
•
ASCENDING and DESCENDING options on the PRIMARY KEY constraint
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),
BUDGET DECIMAL(9),