ALTER TABLE

tags: #sql/schema_changes

To alter the structure of an existing relation (table) in database, we can use the ALRTER TABLE command followed by an action parameter:

ALTER TABLE table_name
  <action>

List of Common Commands

1. ADD COLUMN

The ADD command is used to add a new column to an existing table.

ALTER TABLE table_name 
ADD column_name data_type;

2. DROP COLUMN

To delete an existing column in an existing table:

ALTER TABLE table_name
DROP column_name;

3. MODIFY COLUMN PROPERTY

To modify the data type or size of an existing column in a table:

ALTER TABLE table_name 
MODIFY column_name new_data_type;

-- alternative
ALTER TABLE table_name
ALTER COLUMN column_name SET <constraint type> <new constraint value>

-- example
...
ALTER COLUMN salary SET DATA TYPE INT;
...

4. RENAME TABLE/COLUMN

To rename a table or a column in a table:

-- Rename table
ALTER TABLE old_table_name 
RENAME TO new_table_name;

-- Rename column
ALTER TABLE table_name 
RENAME COLUMN old_column_name TO new_column_name;

5. CONSTRAINT

To add a NAMED constraint:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name <constraint type>;

To add a Primary Key Constraint:

ALTER TABLE table_name
ADD CONSTRAINT pk_constraint_name PRIMARY KEY (column1, column2, ...);

To add a Foreign Key Constraint:

ALTER TABLE table_name 
ADD CONSTRAINT fk_constraint_name FOREIGN KEY (column_in_this_table) REFERENCES parent_table_name (column_in_parent_table);
-- Indicates the parent table and column(s) to which the foreign key refers.

To drop an existing NAMED constraint:

ALTER TABLE table_name 
DROP CONSTRAINT constraint_name CASECADE; 
-- casecade to remove all dependent objects that are also affected by the removal of the constraint.

6. ENABLE/DISABLE TRIGGERS

To enable or disable a trigger on a table:

-- to enable 
ALTER TABLE table_name 
ENABLE TRIGGER trigger_name;

-- to disable
ALTER TABLE table_name
DISABLE TRIGGER trigger_name;