SQL ALTER TABLE statement

SQL ALTER TABLE statement is used to add, modify or delete columns in a table. ALTER TABLE SQL statement is also used to rename a table.

Add a column to a table

SQL syntax of ALTER TABLE statement to add a column to a table.

ALTER TABLE tab_name
ADD column_name_id column_definitionid;

Let’s take a look at the SQL ALTER TABLE example that adds a column. For example.

ALTER TABLE suppl
ADD suppl_name char(50);

This ALTER TABLE SQL example will add a column named suppl_name to the suppl table.

Add multiple columns to the table

SQL ALTER TABLE syntax to add multiple columns to an existing table.

ALTER TABLE tab_name
ADD (column_1_id column_definitionid,
column_2id column_definitionid,
…
column_n_id column_definition_id);

Let’s look at the SQL ALTER TABLE example, which adds more than one column. Let’s take an example.

ALTER TABLE suppl
ADD (suppl_name char(50),
city char(45));

This SQL ALTER TABLE example will add two columns: suppl_name as char(50) and city as char(45) to the suppl table.

Change the column in the table

SQL ALTER TABLE syntax for changing a column in an existing table.

For Oracle, MySQL, MariaDB.

ALTER TABLE tab_name
MODIFY column_name_id column_type_id;

For SQL Server.

ALTER TABLE tab_name
ALTER COLUMN column_name_id column_type_id;

For PostgreSQL.

ALTER TABLE tab_name
ALTER COLUMN column_name_id TYPE column_definition_id;

Let’s see how to change the column named supplier_name using the ALTER TABLE command. Note that in most databases the Syntax is slightly different.

For Oracle.

ALTER TABLE suppl
MODIFY suppl_name char(100) NOT NULL;

For MySQL and MariaDB.

ALTER TABLE suppl
MODIFY suppl_name VARCHAR(100) NOT NULL;

For SQL Server.

ALTER TABLE suppl
ALTER COLUMN suppl_name VARCHAR(100) NOT NULL;

For PostgreSQL.

ALTER TABLE suppl
ALTER COLUMN suppl_name TYPE CHAR(100),
ALTER COLUMN suppl_name SET NOT NULL;

Change several columns in the table

SQL ALTER TABLE syntax for changing several columns in an existing table.

For Oracle.

ALTER TABLE tab_name
MODIFY (column_1_id column_type_id,
column_2_id column_type_id,
…
column_n_id column_type_id);

For MySQL and MariaDB.

ALTER TABLE tab_name
MODIFY column_1_id column_definition_id
[ FIRST | AFTER column_name_id ]
MODIFY column_2_id column_definition_id
[ FIRST | AFTER column_name_id ]
…
;

For PostgreSQL.

ALTER TABLE tab_name
ALTER COLUMN column_name_id TYPE column_definition_id,
ALTER COLUMN column_name_id TYPE column_definition_id,
…
;

Let’s look at an example that uses ALTER TABLE to change more than one column. In this example, we will change two columns with the names supplier_name and city.

For Oracle.

ALTER TABLE suppl
MODIFY (suppl_name char(100) NOT NULL,
city_id char(75));

For MySQL and MariaDB.

ALTER TABLE suppl
MODIFY suppl_name VARCHAR(100) NOT NULL,
MODIFY city_id VARCHAR(75);

For PostgreSQL.

ALTER TABLE suppl
ALTER COLUMN suppl_name TYPE CHAR(100),
ALTER COLUMN suppl_name SET NOT NULL,
ALTER COLUMN city_id TYPE CHAR(75);

Delete column in tables

ALTER TABLE syntax to delete a column in an existing table.

ALTER TABLE tab_name
DROP COLUMN column_name_id;

Let’s take a look at an example that removes a column from a table. For example.

ALTER TABLE suppl
DROP COLUMN suppl_name;

This ALTER TABLE SQL example will remove the suppl_name column from the suppl table.

Rename the column in the table

SQL ALTER TABLE syntax to rename a column in an existing table.

For Oracle and PostgreSQL.

ALTER TABLE tab_name
RENAME COLUMN old_name_id TO new_name_id;

For SQL Server (using stored sp_rename procedure).

sp_rename 'tab_name.old_column_id', 'new_name_id', 'COLUMN';

For MySQL and MariaDB.

ALTER TABLE tab_name
CHANGE COLUMN old_name_id TO new_name_id;

Let’s look at an example that renames a column in a suppl table from suppl_name to sname_id.

For Oracle (9i Rel2 and above) and PostgreSQL.

ALTER TABLE suppl
RENAME COLUMN suppl_name TO sname_id;

For SQL Server (using stored sp_rename procedure).

sp_rename 'suppl.suppl_name', 'sname_id', 'COLUMN';

For MySQL and MariaDB.

ALTER TABLE suppl
CHANGE COLUMN suppl_name sname_id VARCHAR(100);

In MySQL and MariaDB you must specify the column data type when renaming it.

Rename the table

SQL ALTER TABLE syntax for renaming a table.

For Oracle, MySQL, MariaDB, PostgreSQL and SQLite.

ALTER TABLE tab_name
RENAME TO new_tab_name;

For SQL Server (using stored sp_rename procedure).

sp_rename 'tab_name', 'new_tab_name';

Let’s look at an example that renames the supplier table to the new vendor name.

For Oracle, MySQL, MariaDB, PostgreSQL and SQLite.

ALTER TABLE suppl
RENAME TO vend;

For SQL Server (using stored sp_rename procedure).

sp_rename 'suppl', 'vend';

Practical Exercise #1
Based on the departs table below, rename the departs table as deps.

CREATE TABLE depats
( depart_id int NOT NULL,
depart_name char(50) NOT NULL,
CONSTRAINT departs_pk PRIMARY KEY (depart_id)
);

The solution for exercise #1
The next SQL statement ALTER TABLE will rename the table as deps.

ALTER TABLE departs
RENAME TO deps;

Practical Exercise #2
Based on the empls table below, add a column with the name salary_id, which has the int data type.

CREATE TABLE empls
( empl_number int NOT NULL,
empl_name char(50) NOT NULL,
depart_id int,
CONSTRAINT empls_pk PRIMARY KEY (empl_number)
);

The solution for exercise #2
The next ALTER TABLE SQL statement will add the salary_id column to the empls table.

ALTER TABLE empls
ADD salary_id int;

Practical Exercise #3
Based on the table below, add two columns – one column named cont_name, which is char(50) data type, and one column named l_contacted, which has date data type.

CREATE TABLE customs
( custom_id int NOT NULL,
custom_name char(50) NOT NULL,
address_id char(50),
city_id char(50),
state_id char(25),
zip_code_id char(10),
CONSTRAINT customs_pk PRIMARY KEY (custom_id)
);

The solution for exercise #3
The next ALTER TABLE SQL statement will add cont_name and l_contacted columns to the customers table.

ALTER TABLE customs
ADD (cont_name char(50),
l_contacted date);

Practical Exercise #4
Based on the empls table below, change the empl_name column to char(75) data type.

CREATE TABLE empls
( empl_number int NOT NULL,
empl_name char(50) NOT NULL,
depart_id int,
CONSTRAINT empls_pk PRIMARY KEY (empl_number)
);

The solution for exercise #4
The next SQL statement ALTER TABLE will change the data type for the empl_name column to char(75).

ALTER TABLE empls
MODIFY empl_name char(75);

Practical Exercise #5
Based on the customer table below, change the custom_name column to avoid NULL values and change the state_id column to char(2) data type.

CREATE TABLE customs
( custom_id int NOT NULL,
custom_name char(50),
address_id char(50),
city_id char(50),
state_id char(25),
zip_code_id char(10),
CONSTRAINT customs_pk PRIMARY KEY (custom_id)
);

The solution for exercise #5
The next ALTER TABLE SQL statement will change the custom_name and state columns respectively in the customs table.

ALTER TABLE customs
MODIFY (custom_name char(50) NOT NULL,
state_id char(2));

Practical Exercise #6
Based on the table below, remove the salary_id column.

CREATE TABLE empls
( empl_number int NOT NULL,
empl_name char(50) NOT NULL,
depart_id int,
salary_id int,
CONSTRAINT empls_pk PRIMARY KEY (empl_number)
);

Decision for exercise No. 6
The next ALTER TABLE SQL statement will delete the salary_id column from the employees table.

ALTER TABLE empls
DROP COLUMN salary_id;

Practical Exercise #7
Based on the departs table below, rename the depart_name column as_depta.

CREATE TABLE departs
( depart_id int NOT NULL,
depart_name char(50) NOT NULL,
CONSTRAINT departs_pk PRIMARY KEY (depart_id)
);

The solution for exercise #7

The SQL Alter Table Statement