Welcome readers, in this tutorial, we will learn how to ALTER an existing table in the database.
1. Introduction
SQL ALTER command is used to modify the structure of a table in the database. This command allows a developer,
- To add a new column or a constraint to an existing column
- To drop an existing column or a constraint from an existing column
- To modify the datatype or constraint of an existing column
To start with this tutorial, we are hoping that users at present have their preferred database installed on their machines. For easy usage, I am using MySQL on a Windows operating system. If someone needs to go through the MySQL installation, please watch this video.
2. SQL ALTER Table Example
The following tutorial will illustrate the different approaches for running the SQL ALTER command.
2.1 Creating a Sample Table
The following script creates a database named – sql_alter_table_tutorial and a table named – departments.
01 02 03 04 05 06 07 08 09 10 11 12 13 14 | /* Sql to create database. */CREATE DATABASE IF NOT EXISTS sql_alter_table_tutorial; /* Using the newly created database. */USE sql_alter_table_tutorial;/* Creating an departments table. */CREATE TABLE IF NOT EXISTS departments ( department_no INT NOT NULL, department_name VARCHAR(100) NOT NULL, department_hr_id VARCHAR(200), department_hr_name VARCHAR(200) NOT NULL, CONSTRAINT departments_pk PRIMARY KEY (department_no)); |
If everything goes well, the departments table will be created.
2.2 ALTER Table Commands
Programmers can use the following different queries to execute the ALTER commands on a table.
2.2.1 Adding a New Column
The following command will add a new column to the table.
1 2 3 4 5 | /* #1: Query to add a new column to the table. */ALTER TABLE departments ADD COLUMN department_loc VARCHAR(200);/* Query to display the table structure. */DESC departments; |
If everything goes well, the departments table will be updated with a new column named – department_loc.
2.2.2 Dropping an Existing Column
The following command will drop an existing column from the table.
/* #2: Query to drop an existing column from the table. */ ALTER TABLE departments DROP COLUMN department_loc; /* Query to display the table structure. */ DESC departments;
If everything goes well, the departments table will be updated, and the column named – department_loc will be dropped.
2.2.3 Modifying the Datatype of a Column
The following command will modify the datatype of an existing column in the table.
/* #3: Query to modify an existing column in the table. */ ALTER TABLE departments MODIFY COLUMN department_hr_id INT; /* Query to display the table structure. */ DESC departments;
If everything goes well, the departments table will be updated, and the data type of the column named – department_hr_id will be changed to INT.
2.2.4 Adding a Constraint to the Column
The following command will modify the department_hr_id column to add a NOT NULL constraint.
/* #4: Query to alter table to add a constraint to the column. */ ALTER TABLE departments MODIFY COLUMN department_hr_id VARCHAR(100) NOT NULL; /* Query to display the table structure. */ DESC departments;
If everything goes well, the departments table will be updated, and the NOT NULL constraint will be added to the column named – department_hr_id.
2.2.5 Modifying a Constraint on the Column
The following command will modify the existing constraint of a column in the table.
/* #5: Query to alter table to drop a constraint from the column. */ ALTER TABLE departments MODIFY COLUMN department_hr_id VARCHAR(100) NULL; /* Query to display the table structure. */ DESC departments;
If everything goes well, the departments table will be updated, and the NOT NULL constraint will be removed from the column named – department_hr_id.
That is all for this tutorial and I hope the article served you whatever you were looking for. Happy Learning and do not forget to share!
3. Conclusion
In this section, developers learned how to alter an existing table in the database. Developers can download the sample scripts in the Downloads section.
4. Download the SQL Script
This was an example of altering an existing table in the database.
You can download the full source code of this example here: SQL ALTER Table Example
