SQL ALTER TABLE for Beginners

Posted on by Ian

In SQL, the ALTER TABLE statement modifies the definition of an existing table.

You can use ALTER TABLE to alter, add, or drop columns and constraints.

Depending on your DBMS, the ALTER TABLE statement can also be used to reassign and rebuild partitions, or disable and enable constraints and triggers.

Syntax

The ALTER TABLE statement generally goes like this:

ALTER TABLE table_name
    [alter_option [, alter_option] ...]
    [partition_options]

Where:

  • table_name is the name of the table you want to alter.
  • [alter_option [, alter_option] …] is a list of the specific changes you want to make (for example ADD followed by the column name and definition, or DROP COLUMN followed by the column name, etc).
  • [partition_options] is an optional list of options specifically for partitioned tables. Not all DBMSs support partitioned tables. If yours does, such options may enable you to add, drop, discard, import, merge, or split partitions, or to perform partitioning maintenance.

The full syntax for ALTER TABLE can be quite complex and varies significantly between DBMSs. For anything not covered in this article, consult your DBMS documentation.

Below are examples of the most common ALTER TABLE operations.

Add a New Column

To add a new column to a table, use the ADD clause, followed by the column name and data type.

ALTER TABLE Products 
ADD ProductDescription varchar(500);

This adds a new column called ProductDescription to the Products table.

In this example, we made the column a varchar(500) but you would use whatever data type is appropriate for your new column.

You can also include constraints in your column definition, but this may depend on your DBMS, and whether or not the table already contains data (see the discussion below about this).

Rename a Column

Most of the major RDBMSs (PostgreSQL, Oracle, SQLite, MySQL 8.0+, MariaDB 10.5.2+) allow you to rename a column like this:

ALTER TABLE table_name 
RENAME COLUMN column_name TO new_column_name;

If you’re using MySQL prior to version 8.0, or MariaDB prior to 10.5.2+, you’ll need to use the CHANGE COLUMN syntax instead, which also requires you to respecify the datatype. Like this: 

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name datatype;

In SQL Server, you’ll need to use the sp_rename stored procedure to rename a column. Like this:

EXEC sp_rename 'schema_name.table_name.column_name', 'new_column_name', 'COLUMN';

Drop a Column

To drop a column, use the DROP COLUMN clause, followed by the column name.

ALTER TABLE table_name 
DROP COLUMN column_name;

Modify a Column’s Definition

The syntax for modifying an existing column’s definition varies significantly between DBMSs. It also depends on what modifications you need to make.

To keep things simple, let’s do a simple modification to a column in SQL Server:

ALTER TABLE Products 
ALTER COLUMN ProductDescription varchar(1000);

In this example, we changed the ProductDescription column from varchar(500) to varchar(1000).

The following shows the basic syntax required by each DBMS to do the same or similar changes.

SQL Server:

ALTER TABLE table_name 
ALTER COLUMN column_name datatype;

PostgreSQL:

ALTER TABLE table_name 
ALTER COLUMN column_name TYPE datatype;

MySQL, MariaDB, and Oracle prior to 10g:

ALTER TABLE table_name 
MODIFY COLUMN column_name datatype;

In Oracle 10g and later:

ALTER TABLE table_name 
MODIFY column_name datatype;

SQLite won’t let you modify a column, other than to rename it.

The actual syntax for each DBMS is usually much more complex than this, and depends on exactly what you’re trying to do. However, this should get you started with basic column modifications.

Restrictions & Considerations

You should generally avoid making modifications to tables once they contain data. By making changes, you run the risk of losing existing data.

Having said that, many DBMSs actually prevent you from making certain changes once a table contains data. For example, you might find that you can’t add a NOT NULL constraint to a column that contains data.

Some DBMSs may allow you to do so, as long as you use a DEFAULT constraint (to provide a default value for columns that haven’t had data explicitly inserted into them), or an Identity/autoincrement column, or a timestamp column, etc.

Some DBMSs don’t allow you to drop or change columns in a table.

Also, some DBMSs restrict the datatypes that can be added.

Example – Adding a NOT NULL Constraint

Here’s an example to help demonstrate the above points.

Here’s what happens when I try to add a column with a NOT NULL constraint in SQL Server.

ALTER TABLE Products 
ADD ProductDescription varchar(500) NOT NULL;

Result:

Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'ProductDescription' cannot be added to non-empty table 'Products' because it does not satisfy these conditions.

Basically, the table already contains data, and so SQL Server returns an error that tells me I can only add a NOT NULL constraint if the table satisfies certain criteria, and it doesn’t yet satisfy that criteria.

And there’s a good reason for that criteria.

A NOT NULL constraint ensures that there are no NULL values in the column in any row. The problem is, we already have data in the table, and if we add a new column, those existing rows will be NULL – which will immediately violate our NOT NULL constraint. So we need to specify data to go into the existing rows.

To do this, we can add a DEFAULT constraint or similar to ensure that any existing rows are automatically populated with data in this column.

Example:

ALTER TABLE Products 
ADD ProductDescription varchar(500) NOT NULL DEFAULT 'N/A';

This simply adds a DEFAULT constraint to the column. This constraint ensures that all rows contain a value (in this case, the value is N/A) if they haven’t been assigned a value. This means our NOT NULL constraint won’t be violated, because all existing rows will now contain a value.

If the new column needs to have unique, incrementing values, you can instead make it an IDENTITY column (or AUTOINCREMENT in SQLite and other DBMSs).

However, if the table doesn’t already contain data, you might be able to add the column without having to do any of these steps.

Partition Options

This article is aimed towards beginners, and partitioned tables are a bit outside the realm of a beginner’s tutorial.

Having said that, I’ll quickly go over some of the partitioning options with regards to the ALTER TABLE statement.

If your DBMS supports partitioned tables, it probably also provides partition options with the ALTER TABLE statement.

For example, in MySQL you could do the following:

ALTER TABLE Products
PARTITION BY HASH(ProductId)
PARTITIONS 8;

This partitions the table into 8 partitions, by HASH, using the ProductId column as the partitioning key.

PARTITION BY HASH uses the remainder of the expression (in this case the ProductId column) divided by the number of partitions (i.e. the modulus).

You can also use range partitions. Here’s how you might add a range partition to an existing table:

ALTER TABLE Products 
ADD PARTITION (PARTITION Partition9 VALUES LESS THAN (2020));

This example implies that the Products table already has 8 range partitions, and we’re adding another partition called Partition9.

You can drop partitions like this:

ALTER TABLE Products 
DROP PARTITION Partition8, Partition9;

Bear in mind that these examples are for MySQL.

I’ve also written a few articles regarding partitioned tables in SQL Server. With regards to the ALTER TABLE statement, here’s how to switch out a partition and here’s how to switch in a partition (both of which are done with ALTER TABLE).

In SQL Server, some partition options need to be done with other statements. For example merging partitions is done with the ALTER PARTITION FUNCTION statement, and splitting partitions is done with the ALTER PARTITION SCHEME statement.

If you’d like to learn more about partitioned tables in SQL Server, here’s how to create a partitioned table in SQL Server.

ALTER TABLE in SQLite

I should also mention that SQLite has a very limited support of the ALTER TABLE statement. In SQLite, the ALTER TABLE statement allows you to rename a table, to rename a column within a table, or to add a new column to an existing table.

Any other changes will require you to drop the table and start again. This also applies to adding foreign keys.