ALTER (RENAME) in SQL

In SQL, structural modifications to a database are often required, such as renaming tables or columns, adding new columns, or changing data types. The ALTER TABLE command allows these changes to be made efficiently without affecting existing data. It is an essential command for managing and updating database schemas as application requirements evolve.

Example: First, let's create a sample Student table to demonstrate the ALTER command:

Query:

ALTER TABLE students 
RENAME TO learners;  

Output:

Syntax for ALTER Command

Here are the common syntax formats for using the ALTER TABLE command:

1. Renaming a Table

ALTER TABLE table_name
RENAME TO new_table_name;

2. Renaming a Column

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

3. Adding a New Column

ALTER TABLE table_name
ADD column_name datatype;

4. Modifying a Column Data Type

ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;

Examples of ALTER Command in SQL

Below are practical examples to help us understand how to use the ALTER command effectively in various scenarios. These examples includes renaming tables or columns, adding new columns, or changing column data types.

1. Create a Sample Table

First, let's create a sample Student table to demonstrate the ALTER command:

CREATE TABLE Student (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  email VARCHAR(50),
  phone VARCHAR(20)
);

2. Insert Sample Data into the Table

Let's insert some data and then perform ALTER operation to understand better about alter command.

INSERT INTO Student (id, name, age, email, phone) 
VALUES 
(1, 'Amit', 20, 'amit@gmail.com', '9999999999'),
(2, 'Rahul', 22, 'rahul@yahoo.com', '8888888888'),
(3, 'Priya', 21, 'priya@hotmail.com', '7777777777'),
(4, 'Sonia', 23, 'sonia@gmail.com', '6666666666'),
(5, 'Kiran', 19, 'kiran@yahoo.com', '5555555555');

Output

Example 1: Rename a Column

Change the name of column name to FIRST_NAME in table Student. To change the column name of the existing table we have to use Column keyword before writing the existing column name to change.

Syntax

ALTER TABLE Student RENAME COLUMN Column_NAME TO FIRST_NAME;

Query:

ALTER TABLE Student RENAME Column name TO FIRST_NAME;

Output

Example 2: Rename a Table

In this example, we want to rename the table from Student to Student_Details using the ALTER TABLE command, making the name more descriptive and relevant to its content.

Query:

ALTER TABLE Student RENAME TO Student_Details;

Output

Example 3: Add a New Column

To add a new column to the existing table, we first need to select the table with ALTER TABLE command table_name, and then we will write the name of the new column and its datatype with ADD column_name datatype. Let's have a look below to understand better.

Syntax

ALTER TABLE table_name
ADD column_name datatype;

Query:

ALTER TABLE Student ADD marks INT;

Output

5. Modify a Column Data Type

In the example, the phone column is updated from VARCHAR(20) to BIGINT to store numerical data more efficiently and ensure data integrity for phone numbers without unnecessary characters.

Syntax

ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;

Query:

ALTER TABLE Student_Details
MODIFY COLUMN phone BIGINT;

Output

Explanation:

• The phone column now has a BIGINT data type, suitable for storing large numeric values.
  
• Existing data remains unchanged but is stored as integers instead of strings.

Additional ALTER Command Use Cases

1. Removing a Column: In some cases, we might need to remove a column. To do that, you can use the DROP COLUMN syntax:

ALTER TABLE Student_Details

DROP COLUMN marks;

This command deletes the marks column entirely from the table

2. Changing a Column's Default Value: We can also modify a column’s default value using the SET DEFAULT clause:

ALTER TABLE Student_Details

ALTER COLUMN age SET DEFAULT 18;

3. Renaming a Table or Column in Different Databases: Note that SQL syntax can vary across different database systems. Here’s how we would rename a table or column in MySQL, MariaDB, and Oracle:

• MySQL / MariaDB: The syntax for renaming a column is similar, but you must also use the CHANGE COLUMN command to rename a column:

ALTER TABLE Student

CHANGE COLUMN old_column_name new_column_name datatype;

• Oracle: Oracle supports the RENAME COLUMN syntax but requires different syntax for renaming a table:

ALTER TABLE Student RENAME COLUMN old_column_name TO new_column_name;