SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
2-37
Examples of ALTER TABLE
Now that you have the identification, you can drop the foreign key with ALTER
TABLE:
>>alter table staff_m drop constraint STAFF_M_859182618_9541;
--- SQL operation complete.
•
The following command renames an existing table CAT.SCH.T1 to T2:
ALTER TABLE CAT.SCH.T1 RENAME TO T2;
The new ANSI name of the table, T2 is not fully qualified since the table continues
to remain in the same catalog and schema as T1. After the table is renamed, the
fully qualified name of the table is CAT.SCH.T2.
Examples of ALTER TABLE ALTER COLUMN
For a full example of recalibrating an IDENTITY column, see Example of ALTER
TABLE ALTER COLUMN..RECALIBRATE on page 2-38.
Create a table with the IDENTITY column
CREATE TABLE T1 (surrogate_key LARGEINT GENERATED ALWAYS AS IDENTITY
(START WITH 99 INCREMENT BY 1 MAXVALUE 100 MINVALUE 50 NO CYCLE) NOT NULL, b
INT UNSIGNED NOT NULL,
PRIMARY KEY(surrogate_key) );
The third insert will fail with error -8934 as shown here:
insert into T1 values(default,1);
--- 1 row(s) inserted.
insert into T1 values(default,2);
--- 1 row(s) inserted.
>>insert into T1 values(default,3);
*** ERROR[8934] The MAXVALUE for the sequence generator has been exceeded.
--- 0 row(s) inserted.
Alter the table to allow new MAXVALUE and INCREMENT BY values:
ALTER TABLE T1 ALTER COLUMN SURROGATE_KEY SET MAXVALUE 900;
ALTER TABLE T1 ALTER COLUMN SURROGATE_KEY SET INCREMENT BY 2;
insert into T1 values(default,3);
--- 1 row(s) inserted.
select * from T1;
SURROGATE_KEY B
--------------- ----
99 1
100 2
102 3