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.
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)
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.
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.
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 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:
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 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:
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:
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:
|Rename Column||Yes||No – use sp_rename||Yes||Yes|
|Rename Table||Yes||No – use sp_rename||Yes||Yes|
|Alter or Modify Column||MODIFY||MODIFY||MODIFY||ALTER|
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.