Skip to main content
Practice

Altering Tables

The ALTER TABLE command allows you to change the structure of an existing table without deleting or recreating it.

You can add new columns, rename existing ones, or even remove them, all while keeping the data.


Add a Column​

To add a new column to an existing table, you can use the ADD COLUMN command.

Add a new phone column
ALTER TABLE clients
ADD COLUMN phone TEXT;

This adds a phone column to the clients table to store contact numbers.


Rename a Column​

Renaming a column is a common operation when you need to change the name of a column to make it more descriptive or to match your data.

The syntax for renaming a column can vary depending on the database you are using.

For example, SQLite and PostgreSQL support the RENAME COLUMN command, while MySQL uses ALTER TABLE RENAME COLUMN.

Below is an example of how to rename the phone column to contact_number in the clients table.

Rename the phone column
ALTER TABLE clients
RENAME COLUMN phone TO contact_number;

This will change the name of the phone column to contact_number in the clients table.

Note: Some databases require different syntax or do not support this operation at all.


Change a Column's Data Type​

Some SQL engines let you change a column's type:

Change data type of phone
ALTER TABLE clients
ALTER COLUMN contact_number TYPE TEXT;

SQLite, which is the database engine we use at CodeFriends, does not support ALTER COLUMN TYPE directly. Instead, you can use the ALTER TABLE command to change the column's type.


Drop a Column​

To permanently remove a column, you can use the DROP COLUMN command.

Remove the contact_number column
ALTER TABLE clients
DROP COLUMN contact_number;

Be careful — dropping a column deletes its data forever.

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.