Article
home/blog/Loading

Add/Remove/Modify a column on a table in SQL

Sometimes you need to alter a table without dropping it. A big use case for this is a migration script adding or removing a column on a table.

Add a column

You can add a column using the following syntax

ALTER TABLE table_name
    ADD column_name column_type;

Remove a column

Note: IF EXISTS is available to be used here. It will suppress an error from being thrown if the column does not exist on the table.

ALTER TABLE table_name
    DROP COLUMN IF EXISTS column_name;

Adding a constraint to a table

To add a constraint to a table you can do the following:

ALTER TABLE table_name
    ADD CONSTRAINT constraint_name constraint_definition;

For example a parent_id required a valid id

ALTER TABLE table_name
    ADD CONSTRAINT constraint_name FOREIGN KEY (parent_id) REFERENCES table_name(id);