SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-23
Examples of ALTER TABLE
•
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),
CITY CHAR(15),
MGR CHAR(3),
UNIQUE (PNUM),
FOREIGN KEY (MGR)
REFERENCES STAFF_M(EMPNUM));
ALTER TABLE STAFF_M ADD FOREIGN KEY (PRI_WK)
REFERENCES PROJ_M (PNUM);
Suppose further that you now need to drop the foreign key. Use SHOWDDL to
obtain the key’s system identification:
>>showddl staff_m;
CREATE TABLE NIST_EMB_CAT.SUN.STAFF_M
(