SQL alter table: 7+ queries to add, drop, modify columns/constraints

SQL alter table: 7+ queries to add, drop, modify columns/constraints

SQL Alter statement

The alter statement of SQL is used to change the existing table structure of the database.

  • You can use the alter statement to add new columns with column specifications.
  • You can drop or delete existing columns by the alter command.
  • You can use the alter query for modifying existing column specifications. The specifications like data type, size etc.
  • You can also add or drop constraints like the Primary key, not null etc by using the alter command.

Alter example to add column

Alter query to drop column

This tutorial will show you how to add, drop or modify columns in the existing table by using alter SQL command.

Watch a Video of this Tutorial

Syntax of the alter statement

Following is the Alter command syntax to add, modify and delete columns in a table. The syntax is different for MySQL, MS SQL, Oracle and MS Access databases.

Alter table to add column

The general syntax of alter table to add a column:

Alter table table_name

Add col_name datatype(size);

Alter statement to modify column in MySQL

The general syntax of Alter to modify a column in MySQL database is

Alter table table_name

Modify Column col_name datatype|not null ;

i.e. after col_name, the modification you want to make. See examples below for learning how you can use it.

Alter query to modify column in SQL Server and MS Access

The general syntax of Alter command to modify a column in SQL Server and MS Access database is:

Alter table table_name

Alter Column col_name datatype|not null ;

i.e. after the col_name, the modifications you want to make in that column are given. See the working example below.

Alter command to delete a table column 

The general syntax of Alter to delete a column:

Alter table table_name

Drop col_name datatype(size);

Examples of using alter query

Let us show you examples of how to use alter table SQL with our created database and table. We will use the same database, test_db, and tbl_employee table, that we created in the Create table chapter, to show a few examples of Alter statement.

Just to remind this is how our example table is created in test_db:

Now, as we have a table tbl_employee with four columns (emp_id, emp_name, emp_age and emp_salary). Also, we added some data by using the insert statement.

Demo table before Alter statement 

The alter command to add column example

In this example, we will add a new column joining_date to the existing tbl_employee table. This is how the alter query to add a column is:

SQL alter table

Demo Table after adding new column

As you can see, a new column is added in the table. As the table is retrieved by using the select statement, it is showing the joining_date column with the null values. One more thing to be noted is the joining_date column cannot be created using the not null table constraint without the Default definition, as such, the table already contains a few rows.

Alter table to drop a column example

This example shows how to drop an existing column by using the alter table drop column query. For this example, we will drop the same column, joining_date that we just created in the above example.

SQL alter table drop column

The Alter with drop column query will be:

Demo Table after drop column

You can see the graphic; table before and after running the Alter statement to drop a column.

Example of using Alter/Modify column in table

As we have seen in the syntax of Alter statement to modify columns that MySQL, Oracle and SQL server/ MS Access has different syntax. We will show you modify/alter the columns in both ways.

Alter statement to modify column in SQL Server/MS Access

Alter datatype of a column

In this example, we will alter the datatype of joining_date from the datetime to date. So it will be able to store the date values.

The alter query will be as follows:

Alter column to add the Not Null constraint in a column:

Alter query to add Primary key in table column

Suppose our table has no primary key and we want to add primary key on the emp_id column. This is how our Alter query will be:

Alter query to drop primary key constraint

Alter column in MySQL / Oracle example

Alter datatype of a column

In this example, we will alter the datatype of joining_date from the datetime to date. So it will be able to store date. The query in the MySQL or Oracle database will be:

Alter column to add the Not Null constraint in a column:

Alter query to add Primary key in table column

This is how our SQL query will be for adding primary key in the MySQL and Oracle database:

Alter query to drop primary key constraint

Also see: SQL Update statement