ALTER Command in DB2

Syntax

ALTER TABLE table_name
  [ADD COLUMN column_name datatype [DEFAULT default_value] [NULL|NOT NULL]
   | DROP COLUMN column_name
   | MODIFY COLUMN column_name datatype [DEFAULT default_value] [NULL|NOT NULL]
   | ALTER COLUMN column_name SET DATA TYPE datatype
   | ADD PRIMARY KEY (column_name, ...)
   | DROP PRIMARY KEY
   | ADD UNIQUE (column_name, ...)
   | DROP UNIQUE
   | ADD FOREIGN KEY (column_name, ...) REFERENCES ref_table (ref_column, ...)
   | DROP FOREIGN KEY
   | RENAME TO new_table_name
   | ADD CHECK (condition)
   | DROP CHECK constraint_name];

Example

-- Add a new column to the 'employees' table
ALTER TABLE employees
  ADD COLUMN birth_date DATE;

-- Modify the data type of the 'salary' column
ALTER TABLE employees
  MODIFY COLUMN salary DECIMAL(10, 2);

-- Add a primary key to the 'departments' table
ALTER TABLE departments
  ADD PRIMARY KEY (department_id);

Output

The ALTER command in DB2 using RazorSQL Tool will execute the specified modification on the table, producing no direct output unless there are errors or constraints violations.

Explanation

The ALTER command in DB2 is used to modify the structure of a table. It allows you to add or drop columns, modify data types, add or drop constraints (such as primary keys, unique constraints, and foreign keys), rename tables, and more.

Use

• Adding Columns: Use ADD COLUMN to add a new column to an existing table.
• Dropping Columns: Use DROP COLUMN to remove a column from a table.
• Modifying Columns: Use MODIFY COLUMN to change the data type or other properties of an existing column.
• Adding Constraints: Use ADD PRIMARY KEY, ADD UNIQUE, or ADD FOREIGN KEY to enforce data integrity.
• Renaming Tables: Use RENAME TO to change the name of an existing table.

Important Points

• Ensure that the modifications you make with the ALTER command are compatible with the existing data in the table.
• Be cautious when dropping columns or constraints, as it may result in data loss or affect referential integrity.
• Use the ALTER COLUMN syntax to modify the data type of an existing column.

Summary

The ALTER command in DB2 using RazorSQL Tool is a powerful tool for making structural changes to database tables. It offers a variety of options for adding, modifying, or removing elements from a table, allowing for flexibility in database schema management.