ALTER TABLE

ALTER TABLE changes the definition of an existing table without dropping and recreating it.

What ALTER TABLE can do

• ALTER TABLE is a DDL statement used to change a table after it is created.
• Common changes are adding a new column, adding or dropping a primary key, adding a foreign key, and changing the AUDITING or DATA CAPTURE options.
• The table keeps its data. You do not lose rows when you alter the table.
• Some changes put the tablespace in a restrictive state, for example REORG pending, and you must run the REORG utility before the table can be used normally.

Adding a column

• The most common ALTER TABLE is adding a new column:-
• ALTER TABLE MM01.EMP
     ADD COLUMN MIDINIT CHAR(1) NOT NULL WITH DEFAULT;
• The new column is added at the end of the row. Existing rows get the default value or null for the new column.
• If the table already has data, define the new column as nullable or WITH DEFAULT, otherwise DB2 rejects the ALTER.

Changing keys and constraints

• You can add a primary key or a foreign key to an existing table:-
• ALTER TABLE MM01.EMP
     ADD PRIMARY KEY (EMPNO);
  
  ALTER TABLE MM01.EMP
     ADD FOREIGN KEY (WORKDEPT)
     REFERENCES MM01.DEPT (DEPTNO);
• Before adding a primary key, make sure the existing data has no duplicate or null values in the key columns, or the ALTER fails.
• Adding a foreign key checks existing rows against the parent table. Rows that break the rule stop the ALTER.