Altering & deleting columns

TLDR

Change column data types, rename columns, and modify constraints using ALTER TABLE.

Safely remove columns with DROP COLUMN, including considerations for data loss and foreign key constraints. Follow best practices such as backing up your data and testing in a development environment before making changes to your schema.

1. Altering Columns

The ALTER TABLE statement allows you to modify an existing table structure. You can use it to change column types, rename columns, or add constraints.

a. Changing the Data Type of a Column

If you need to change the data type of a column, you can use the ALTER COLUMN statement with SET DATA TYPE. For example, if you decide that postal_code in the street table should be an integer instead of varchar, you can do the following:


ALTER TABLE street
ALTER COLUMN postal_code SET DATA TYPE integer;

This changes the postal_code column from a varchar type to an integer type.

b. Renaming a Column

If you want to rename a column, you can use the RENAME COLUMN option. For example, if you want to rename the address column in the houses table to street_address:


ALTER TABLE houses
RENAME COLUMN address TO street_address;

This changes the column name from address to street_address in the houses table.

c. Modifying Constraints on a Column

You can also modify constraints on columns. For example, if you want to make the firstname in the persons table a NOT NULL column (ensuring that every person has a first name):


ALTER TABLE persons
ALTER COLUMN firstname SET NOT NULL;

This ensures that the firstname field cannot be left empty when inserting or updating records.

2. Deleting Columns

Sometimes you might need to remove a column from a table. The ALTER TABLE statement is also used for this, but with the DROP COLUMN clause.

a. Deleting a Single Column

Let’s say you decide that the description column in the street table is no longer needed:


ALTER TABLE street
DROP COLUMN description;

This removes the description column from the street table.

b. Deleting Multiple Columns

You can drop multiple columns in a single statement by separating the column names with commas:


ALTER TABLE persons
DROP COLUMN middle_name, DROP COLUMN city;

This removes both the middle_name and city columns from the persons table.

c. Caution When Dropping Columns

Data loss: When you drop a column, all the data stored in that column is permanently deleted. Make sure you no longer need the data before dropping a column.

Foreign key considerations: If a column is part of a foreign key relationship, you may not be able to drop it unless you first remove the foreign key constraint.

4. Practical Example: Modifying Your Schema

Let’s say you want to make the following modifications to your schema:

Rename the address column in houses to street_address.

Add a new column state to the street table.

Drop the description column from the street table.

Here’s how you would perform each operation:

Rename `address` to `street_address` in `houses`:


ALTER TABLE houses
RENAME COLUMN address TO street_address;

Add a `state` column to the `street` table:


ALTER TABLE street
ADD COLUMN state varchar(50);

Drop the `description` column from the `street` table:


ALTER TABLE street
DROP COLUMN description;