Adding, Dropping, and Modifying Columns
The ALTER TABLE command statement in SQL database management systems is used to alter or modify the structure of existing database tables. Database users can apply several table operations related to modifying table columns. For example, adding new columns to an existing table, deleting existing table columns, or modifying the existing properties of existing table columns.
Let’s explore the steps for adding, deleting, and modifying columns in SQL database tables.
Adding a New Column to an SQL Database Table.
To add a new column to an existing SQL database table, database users can apply the ALTER TABLE command or statement with the ADD keyword. The ALTER TABLE command helps database users modify or upgrade an existing table by adding one or more new table columns with specific data type formats and table constraints.
Syntax for adding a new column to a table.
ALTER TABLE table_name
ADD column_name datatype [constraint];
Example of adding a single column to a database table.
ALTER TABLE employe
ADD address VARCHAR(200);
In this example.
- Here, a new address column with the VARCHAR(200) text data type has been added to the employe table.
- No constraint has been specified for the employee table in this example; therefore, this table column allows NULL values by default, unless otherwise specified.
Example of adding multiple columns to an SQL database table.
Database users can add multiple columns to an SQL table simultaneously.
ALTER TABLE employe
ADD website VARCHAR(120),
join_date DATE;
In this example.
- Here, the website table column with VARCHAR(120) data type and the join_date column with DATE data type format have been added to the employe table.
Deleting Columns in an SQL Database Table.
To delete a column from an existing table in an SQL database, the database user can apply the DROP COLUMN clause with the ALTER TABLE command or statement. Once a column is deleted from a database table, all the data records in that table column are permanently deleted.
Syntax for dropping a column in a SQL Database Table.
ALTER TABLE table_name
DROP COLUMN column_name;
Example of dropping a column in a SQL Database Table.
ALTER TABLE employe
DROP COLUMN website;
In this example.
- Here, the ‘website’ column has been deleted from the ’employe’ table.
Remember.
Some other popular database management system software, such as SQL Server, allow database users to delete multiple table columns simultaneously.
ALTER TABLE employe
DROP COLUMN website, join_date;
However, in popular database software like MySQL, the database user may need to execute a separate ALTER TABLE statement for each table column they want to delete from the table.
Modifying Database Table Columns.
Database users can apply the ALTER TABLE command statement to modify existing columns in an SQL table. This process includes modifying the data type of an existing table column, modifying the name of an existing table column, or modifying or updating the constraints of a table column. For example, defining a table column as NOT NULL or modifying its default value.
Syntax for modifying database table columns.
ALTER TABLE table_name
MODIFY column_name new_datatype [new_constraints];
Example of changing the data type of a table column.
ALTER TABLE employe
MODIFY website VARCHAR(140);
In this example.
- In this example, the data type of the ‘website’ column has been modified from VARCHAR(120) to VARCHAR(140).
Example of changing the default value of a table column.
Database users can also modify the default value of a table column. ALTER TABLE employee
ALTER COLUMN join_date SET DEFAULT ‘2026-02-04’;
This example sets a new default value for the `join_date` table column. If no value is provided during the data insertion process, this table column will default to the date value ‘2026-02-04’.
Example of changing constraints on a column in a database table.
Database users can also modify a table column to apply new table column constraints, such as adding a NOT NULL constraint.
ALTER TABLE employee
MODIFY website VARCHAR(140) NOT NULL;
This example makes the `website` table column mandatory; it cannot accept NULL values in the table.
To rename a database table column (database-specific).
In some other popular database software, such as MySQL and PostgreSQL, the software allows database users to alter or modify the name of a table column using the RENAME keyword. Remember that this syntax may vary between different database systems.
MySQL database software rename.
ALTER TABLE employee
CHANGE COLUMN old_column_name new_column_name new_datatype;
Example of MySQL database software rename.
ALTER TABLE employee
CHANGE COLUMN address location VARCHAR(240);
In this example.
- the table column named “address” has been modified to “location”.
- The syntax in PostgreSQL database management software is slightly different.
ALTER TABLE employee
RENAME COLUMN old_column_name TO new_column_name;
Example of PostgreSQL database management software table rename.
ALTER TABLE employee
RENAME COLUMN address TO location;
Things to keep in mind when modifying tables in SQL database management systems.
- Data Loss – When a user drops a table column in a database table, the data stored in that column is lost. Therefore, always back up any useful table data before dropping a column in a database table.
- Constraints and Default Values - If the database user is modifying the data type of a table column, first ensure that the new data type is fully compatible with the existing column data. When adding constraints, such as NOT NULL, first ensure that the existing table column data adheres to these constraints, otherwise the applied table operation may fail.
- Renaming a Column – Renaming an existing table column may impact existing table queries, views, or stored procedures that use the old table column name. So, first ensure that all table column dependencies are updated accordingly.
Conclusion of Adding, Dropping, and Modifying Columns in SQL.
- In SQL database management systems, the ALTER TABLE command or statement allows database users to modify the structure of an existing table. This includes adding new table columns to extend the table with new table column fields.
- It also includes dropping table columns to delete unnecessary table fields in existing database tables.
- In SQL database management systems, it includes modifying columns to change table data types, add constraints to tables, or modify the names of existing table columns, etc.
