SQL ALTER TABLE

This tutorial shows you how to use the SQL ALTER TABLE to change the structure of existing tables in the database.

Once you create a new table, you may want to change its structure because business requirements change. To modify the structure of a table, you use the ALTER TABLE statement. The ALTER TABLE statement allows you to perform the following operations on an existing table:

• Add a new column using the ADD clause.
• Modify attribute of a column such as constraint, default value, etc. using the MODIFY clause.
• Remove columns using the DROP clause.

We will examine each operation in detail in the following sections.

SQL ALTER TABLE ADD column

The following statement illustrates the ALTER TABLE with the ADD clause that allows you to add one or more columns to a table.

ALTER TABLE table_name
ADD new_colum data_type column_constraint [AFTER existing_column];

To add one or more columns to a table, you need to perform the following steps:

• First, specify the table that you want to add column denoted by the table_name after the ALTER TABLE clause.
• Second, place the new column definition after the ADD clause. If you want to specify the order of the new column in the table, you can use the optional clause AFTER existing_column.

Note that if you omit the AFTER clause, all the new columns will be added after the last column of the table.

Let’s look at some examples of adding new columns to the courses table that we created in the create table tutorial

The following statement adds a new column named credit_hours to the courses table.

ALTER TABLE courses ADD credit_hours INT NOT NULL;

You can add multiple columns to a table using a single ALTER TABLE statement. For example, The following statement adds the fee and max_limit columns to the courses table and places these columns after the course_name column.

ALTER TABLE courses 
ADD fee NUMERIC (10, 2) AFTER course_name,
ADD max_limit INT AFTER course_name;

SQL ALTER TABLE MODIFY column

The MODIFY clause allows you to change some attributes of the existing column e.g., NOT NULL ,UNIQUE, and data type.

The following statement shows you the syntax of the ALTER TABLE statement with the DROP clause.

ALTER TABLE table_name
MODIFY column_definition;

Notice that you should modify the attributes of columns of a table that has no data. Because changing the attributes of a column in a table that already has data may result in permanent data loss.

For example, if the data type of the column is VARCHAR, and you change it to INT, the database system has to convert the data from VARCHAR to INT. If the conversion fails, the database system may use the default value of the column, which may not be what you expected.

The following ALTER TABLE MODIFY statement changes the attribute of the fee column to NOT NULL.

ALTER TABLE courses 
MODIFY fee NUMERIC (10, 2) NOT NULL;

SQL ALTER TABLE DROP columns

When a column of a table is obsolete and not used by any other database objects such as triggers, views, stored and stored procedures, you need to remove it from the table.

To remove one or more columns, you use the following syntax:

ALTER TABLE table_name
DROP column_name,
DROP colum_name,
...

For example, to remove the fee column of the courses table, you use the following statement.

ALTER TABLE courses DROP COLUMN fee;

To remove more than one column at the same time, you use multiple DROP COLUMN clauses separated by a comma (,).

For example, the following statement removes the max_limit and credit_hours of the courses table.

ALTER TABLE courses 
DROP COLUMN max_limit,
DROP COLUMN credit_hours;