SQL ALTER TABLE: A Complete Guide

The Oracle ALTER TABLE statement allows you to make changes to an existing table. Learn how to use it and see some examples in this guide.

This article applies to Oracle, SQL Server, MySQL, and PostgreSQL.

What Is The SQL ALTER TABLE Statement?

The SQL ALTER TABLE statement lets you change a table that has already been created.

Using the CREATE TABLE statement lets you create a table, but using ALTER TABLE lets you change it without dropping the table and recreating it.

What Can and Can’t You Do with the ALTER TABLE Statement?

There are many things you can do with the SQL ALTER TABLE statement and some that you can’t.

With this command, you can:

• Add one or more columns to a table
• Change the data type of one or more columns
• Add a constraint to a column
• Drop a column from a table
• Rename a column
• Rename a table
• Much more

For now, this article will focus on the most common uses for ALTER TABLE. Over time, I’ll update this article to add more information to it.

ALTER TABLE Syntax

The syntax of the SQL ALTER TABLE command is:

ALTER TABLE [schema.]tablename alter_table_clause;

There are many different clauses, which we’ll discuss in this article, along with some examples.

ALTER TABLE Add Column

Sometimes, you might want to add a column to a table. You can do this without dropping and creating the table by using the ALTER TABLE SQL statement.

The ALTER TABLE syntax to do this is:

ALTER TABLE table_name
ADD [COLUMN] column_name column_definition;

The parameters are:

• table_name: the name of the table you’re changing.
• column_name: the name of the new column you’re adding.
• column_definition: information about the new column you’re adding.

Some things to note about this command are:

• You don’t need to specify ADD COLUMN. Just the ADD word is enough.
• Column names need to be less than the maximum number of allowed characters, which is 128 for Oracle and SQL Server, 64 for MySQL, and 63 for PostgreSQL.

The column_definition is similar to that from the CREATE TABLE statement. It can contain a few things:

• The data type and precision of the new column
• Any constraints that apply to the table (e.g. NOT NULL)

Examples

Let’s see some examples of the SQL ALTER TABLE Add Column functionality.

This example adds a new column called favourite_subject to the student table.

ALTER TABLE student
ADD favourite_subject VARCHAR2(100);

ALTER TABLE student
ADD favourite_subject VARCHAR(100);

ALTER TABLE student
ADD favourite_subject VARCHAR(100);

ALTER TABLE student
ADD favourite_subject VARCHAR(100);

I’ve made it a VARCHAR (or VARCHAR2) column with 100 characters, so it can store a text value for a subject name here.

All existing values will have a value of NULL for this field.

Let’s see another example using a different data type and a constraint.

This example adds a new column called difficulty_rating.

ALTER TABLE subject
ADD difficulty_rating NUMBER(2) NOT NULL;

ALTER TABLE subject
ADD difficulty_rating INT NOT NULL;

ALTER TABLE subject
ADD difficulty_rating INT(2) NOT NULL;

ALTER TABLE subject
ADD difficulty_rating INTEGER NOT NULL;

This new column is a number data type. I’ve also marked it as NOT NULL, which means it needs a value.

What happens to the existing values in a table, if you add a column with NOT NULL?

You will get an exception because the existing rows will have a value of NULL.

To avoid this, specify a default value.

ALTER TABLE subject
ADD difficulty_rating NUMBER(2) NOT NULL DEFAULT 99;

ALTER TABLE subject
ADD difficulty_rating INT NOT NULL DEFAULT 99;

ALTER TABLE subject
ADD difficulty_rating INT(2) NOT NULL DEFAULT 99;

ALTER TABLE subject
ADD difficulty_rating INTEGER NOT NULL DEFAULT 99;

ALTER TABLE Add Multiple Columns

You can add multiple columns to a table using a single ALTER TABLE SQL command. This is better for performance than running separate statements. It works on Oracle, SQL Server, MySQL, and PostgreSQL.

To do this, just separate your columns with commas and include them in brackets.

The syntax for adding multiple columns to a table is:

ALTER TABLE table_name
ADD (column_name column_definition,
column_name2 column_definition2,
...
column_name_n, column_definition_n);

ALTER TABLE table_name
ADD column_name column_definition,
column_name2 column_definition2,
...
column_name_n, column_definition_n;

ALTER TABLE table_name
ADD column_name column_definition,
column_name2 column_definition2,
...
column_name_n, column_definition_n;

ALTER TABLE table_name
ADD column_name column_definition,
column_name2 column_definition2,
...
column_name_n, column_definition_n;

The syntax is the same between these four vendors, except for Oracle where brackets need to surround the column definitions.

This works in the same way as the ALTER TABLE command for a single column, except you can specify multiple column names, each with their own definition.

An example of this would be:

ALTER TABLE student
ADD (
  favourite_subject VARCHAR2(100),
  date_of_birth DATE,
  login_id NUMBER(6)
);

ALTER TABLE student
ADD (
  favourite_subject VARCHAR(100),
  date_of_birth DATE,
  login_id INT
);

ALTER TABLE student
ADD (
  favourite_subject VARCHAR(100),
  date_of_birth DATE,
  login_id INT(6)
);

ALTER TABLE student
ADD (
  favourite_subject VARCHAR(100),
  date_of_birth DATE,
  login_id INTEGER
);

ALTER TABLE DROP COLUMN

To remove a column from an existing table in SQL, you use the ALTER TABLE DROP COLUMN command. It’s part of the ALTER TABLE command and uses the standard “drop” word from removing something from an object.

The syntax for dropping a column is:

ALTER TABLE tablename
DROP [COLUMN] column_1 [, column_n] [CASCADE CONSTRAINTS] ;

ALTER TABLE tablename
DROP [COLUMN] column_1 [, column_n];

ALTER TABLE tablename
DROP [COLUMN] column_1 [, column_n] ;

ALTER TABLE tablename
DROP [COLUMN] column_1 [, column_n] [RESTRICT | CASCADE] ;

Some things to note about this syntax:

• After the tablename, you have the word DROP.
• The word COLUMN after the DROP is optional.
• If you specify the COLUMN keyword, you don’t need brackets for the column names.
• If you don’t specify the COLUMN keyword, you must specify brackets for the column names.
• You can specify more than one column to drop. Just use commas to separate the columns.

In Oracle, If there are columns that refer to the dropped columns using constraints, then you need to specify CASCADE CONSTRAINTS at the end of the statement. If this is not done, you will get an error. If you don’t have any columns that have these referential constraints, then you don’t need the CASCADE CONSTRAINTS clause.

In PostgreSQL, there are two parameters at the end of the statement:

• CASCADE: Automatically drop objects that depend on the table (such as views) and then all objects that depend on those objects.
• RESTRICT: Refuse to drop the table if any objects depend on it. This is the default.

Here are some examples of using the ALTER TABLE DROP COLUMN command to drop columns:

ALTER TABLE student DROP COLUMN favourite_subject;

ALTER TABLE student DROP (favourite_subject);

ALTER TABLE student DROP (favourite_subject, difficulty_rating);

ALTER TABLE student DROP COLUMN (favourite_subject, date_of_birth, login_id);

There are some restrictions on dropping columns from a table:

• You can’t drop a column that’s part of the partition key for a partitioned table.
• You can’t drop a column that’s part of the primary key

ALTER TABLE MODIFY COLUMN or ALTER COLUMN

You can also use the ALTER TABLE command to modify a column on a table.

The syntax for this is:

ALTER TABLE tablename MODIFY column_name data_type;

ALTER TABLE tablename MODIFY column_name data_type;

ALTER TABLE tablename MODIFY column_name data_type;

ALTER TABLE tablename ALTER column_name data_type;

Notice that PostgreSQL uses the ALTER keyword and other vendors use the MODIFY keyword.

You can also modify multiple columns in a single statement:

ALTER TABLE tablename MODIFY (
column_1 data_type_1
...
column_n data_type_n
);

The ALTER TABLE MODIFY COLUMN (or ALTER TABLE ALTER COLUMN) command allows you to make the following kinds of changes:

• Change the data type of an existing column
• Add constraints to existing columns (such as NOT NULL)
• Set the default value

To rename a column, you use the ALTER TABLE RENAME COLUMN command, which is detailed below.

Let’s see some examples of this.

ALTER TABLE student MODIFY COLUMN favourite_subject NUMBER(5);

ALTER TABLE student MODIFY COLUMN favourite_subject VARCHAR2(500);

ALTER TABLE student MODIFY COLUMN (
  favourite_subject NUMBER(10),
  date_of_birth VARCHAR2(8)
);

ALTER TABLE student MODIFY COLUMN favourite_subject NOT NULL;

ALTER TABLE RENAME COLUMN

If you want to rename a column, there is a specific command for that – ALTER TABLE RENAME COLUMN.

It’s a simple command to run, but it can be hard to remember.

The syntax for doing this is:

ALTER TABLE table_name RENAME COLUMN column1 TO column2;

Some examples of this would be:

ALTER TABLE student RENAME COLUMN favourite_subject TO favourite_subject_id;

ALTER TABLE student RENAME COLUMN last_name TO surname;

These two statements will rename the columns inside the student table.

This works for Oracle, MySQL, and PostgreSQL. In SQL Server, to rename a column you need to use the sp_rename function.

ALTER TABLE ADD PRIMARY KEY

Another thing you can do with the ALTER TABLE command is add primary keys.

You can’t add a primary key using the MODIFY COLUMN command because this is done using the ALTER TABLE ADD PRIMARY KEY syntax.

You can actually add all kinds of constraints using this syntax.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (columns) [parameters];

Some things to note about this syntax:

• You can add all five types of constraints using this syntax (Primary Key, Foreign Key, Not Null, Unique, and Check Constraint)
• The parameters at the end will depend on the type of constraint you’re adding
• You can add a constraint name which is recommended, as it makes it easier to work with constraints.

Let’s see some examples of adding a primary key.

ALTER TABLE student
ADD CONSTRAINT pk_student_stdid PRIMARY KEY (student_id); 

In this example, I’ve added a primary key called “pk_student_stdid” on the student_id column in the student table.

I like to name my constraints using a consistent naming standard. The pk stands for Primary Key, then an underscore, then the table name, then another underscore, then a short version of the column.

ALTER TABLE ADD FOREIGN KEY

You can add a foreign key to a table using the ALTER TABLE command.

It works similar to adding a primary key, and similar to the foreign key command within the CREATE TABLE command.

The syntax is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (columns) [parameters]

So, to add a foreign key, your statement could look like this:

ALTER TABLE student
ADD CONSTRAINT fk_student_addid
FOREIGN KEY (address_id) REFERENCES address(address_id);

This statement will add a foreign key on the address_id field in the student table. It will point to the address_id column in the address table.

ALTER TABLE DROP CONSTRAINT

To drop a constraint using the ALTER TABLE command, you use a statement like this:

ALTER TABLE tablename
DROP constraint_type constraint_name [CASCADE];

Just like with many of the other examples, you start with ALTER TABLE and then specify the table name.

Then, you add the word DROP, and then the constraint type. This can be the constraint type if you know it (such as PRIMARY KEY or UNIQUE), or just the word CONSTRAINT.

You then specify the name of the constraint to drop.

Finally, the optional CASCADE keyword can be provided when dropping primary keys, and it will instruct Oracle to drop any foreign keys that reference the primary key.

An example of the ALTER TABLE DROP CONSTRAINT command is:

ALTER TABLE student
DROP CONSTRAINT fk_address_addid;

Or, we can use the constraint type directly:

ALTER TABLE student
DROP PRIMARY KEY pk_student_id;

ALTER TABLE RENAME TABLE

Another task you can do with the ALTER TABLE command is renaming tables.

The syntax for renaming a table is:

ALTER TABLE tablename RENAME TO new_tablename;

It’s a simple statement, but it’s easy to forget the syntax for it.

An example of this command is

ALTER TABLE student RENAME TO person;

Another example could be:

ALTER TABLE subject RENAME TO old_subject;

This works for Oracle, MySQL, and PostgreSQL. In SQL Server, to rename a table you need to use the sp_rename function.

Summary of Differences Between Each Vendor

This table summarises the differences with ALTER TABLE for each database vendor:

Conclusion

So, that brings us to the end of the guide to the ALTER TABLE statement. It’s a powerful statement that has a lot of options.