SQL ALTER TABLE statement

Preamble

A table’s columns can be altered, added, or removed using the SQL ALTER TABLE statement. A table can also be renamed using the SQL statement ALTER TABLE.

Add a column to a table

The ALTER TABLE statement’s SQL syntax for adding a column to a table

ALTER TABLE tab_name
ADD column_name_id column_definitionid;

Let’s examine the SQL ALTER TABLE example that includes the addition of a column. a case study.

ALTER TABLE suppl
ADD suppl_name char(50);

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

Add multiple columns to the table

Use the SQL ALTER TABLE syntax to add a lot of columns to a table that already exists.

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 several new columns. Take this as a case study.

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

Use the SQL ALTER TABLE syntax to modify a column in an existing table.

for MariaDB, MySQL, and Oracle.

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 look at how to use the ALTER TABLE command to modify the supplier_name column. Please take note that the Syntax varies slightly between most databases.

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

For changing multiple columns in an existing table, use the SQL ALTER TABLE syntax.

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 examine an instance where more than one column was changed using the ALTER TABLE command. We’ll modify two columns in this example 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 delete the suppl_name column from the suppl table.

Rename the column in the table

A column in an existing table can be renamed using the SQL ALTER TABLE syntax.

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 examine a scenario where a column in a suppl table is renamed 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);

When you rename a column in MySQL or MariaDB, you have to say what kind of data it holds.

Rename the table

The syntax for renaming a table in SQL is ALTER 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 take a look at an example where the supplier table is renamed to reflect 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
Rename the departs table deps based on the departs table below.

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 table will be renamed to deps in the following SQL statement ALTER TABLE.

ALTER TABLE departs
RENAME TO deps;

Practical Exercise #2
Create a column with the name salary_id and the data type int based on the empls table below.

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 following ALTER TABLE SQL statement will add the salary_id column to the empls table.

ALTER TABLE empls
ADD salary_id int;

Practical Exercise #3
Add two columns based on the table below: one named “cont_name,” which has a char(50) data type, and one named “l_contacted,” which has a 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 cont_name and l_contacted columns will be added to the customers table by the following ALTER TABLE SQL statement.

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

Practical Exercise #4
Change the empl_name column to a char(75) data type based on the empls table below.

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 empl_name column’s data type will be changed to char(75) in the following SQL statement ALTER TABLE.

ALTER TABLE empls
MODIFY empl_name char(75);

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

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 custom_name and state columns in the customs table will be modified in the following ALTER TABLE SQL statement.

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

Practical Exercise #6
Remove the salary_id column based on the following table.

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 salary_id column will be removed from the employees table using the following ALTER TABLE SQL statement.

ALTER TABLE empls
DROP COLUMN salary_id;

Practical Exercise #7
Rename the depart_name column to as_depta based on the departs table below.

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 depart_name column in the departs table will be renamed by the following SQL statement, ALTER TABLE.

The SQL Alter Table Statement

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.