Altering a column that is part of an index

For example, suppose you create tables and alter the parent table with the following SQL statements:

CREATE TABLE parent 
   (si SMALLINT PRIMARY KEY CONSTRAINT pkey);
CREATE TABLE child
   (si SMALLINT REFERENCES parent ON DELETE CASCADE
   CONSTRAINT ckey);
INSERT INTO parent (si) VALUES (1); 
INSERT INTO parent (si) VALUES (2); 
INSERT INTO child (si) VALUES (1); 
INSERT INTO child (si) VALUES (2); 
ALTER TABLE parent
   MODIFY (si INT PRIMARY KEY CONSTRAINT pkey);

This ALTER TABLE example converts a SMALLINT column to an INT column. The database server retains the primary key because the ALTER TABLE statement specifies the PRIMARY KEY keywords and the pkey constraint. When you specify a PRIMARY KEY constraint in the MODIFY clause, the database server also silently creates a NOT NULL constraint on the same primary key column. However, the database server drops any referential constraints to that primary key. Therefore, you must also specify the following ALTER TABLE statement for the child table:

ALTER TABLE child 
   MODIFY (si int references parent on delete cascade
         constraint ckey);

Even though the ALTER TABLE operation on a primary key or foreign key column rebuilds the index, the database server still takes advantage of the in-place alter algorithm. The in-place alter algorithm can provide performance benefits, including the following:

• It does not make a copy of the table in order to convert the table to the new definition.
• It does not convert the data rows during the alter operation.
• It does not rebuild all indexes on the table.