SQL ALTER TABLE

SQL ALTER TABLE Statement

The SQL ALTER TABLE statement is used to modify the structure of an existing table. This command allows you to add, delete, or modify columns, rename the table, and change constraints, offering flexibility in managing and updating database schemas.

---

Syntax

To add a column:

ALTER TABLE table_name
ADD column_name datatype;

To delete a column:

ALTER TABLE table_name
DROP COLUMN column_name;

To modify a column:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

To rename a column:

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

To rename the table:

ALTER TABLE old_table_name
RENAME TO new_table_name;

• ALTER TABLE: This is the SQL keyword used to modify a table.
• table_name: This specifies the name of the table you want to modify.
• column_name: This specifies the name of the column you want to add, delete, or modify.
• datatype: This specifies the type of data the column can hold, such as INTEGER, VARCHAR, DATE, etc.
• old_name: This specifies the current name of the column or table you want to rename.
• new_name: This specifies the new name you want to assign to the column or table.

---

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, and then using various ALTER TABLE commands.

Step 1: Creating a Database

This step involves creating a new database where the table will be stored.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table

In this step, we create a table named employees within the previously created database.

USE example_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE
);

Here, we define the employees table with columns for id, first_name, last_name, email, and hire_date. The id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01');

Here, we insert two rows of data into the employees table.

Step 4: Adding a Column

This step involves adding a new column named salary to the employees table.

ALTER TABLE employees
ADD salary DECIMAL(10, 2);

This command will add a salary column to the employees table with a data type of DECIMAL(10, 2).

Step 5: Modifying a Column

This step involves modifying the email column to increase its length.

ALTER TABLE employees
MODIFY COLUMN email VARCHAR(150);

This command will change the email column to have a maximum length of 150 characters.

Step 6: Renaming a Column

This step involves renaming the hire_date column to date_of_hire.

ALTER TABLE employees
RENAME COLUMN hire_date TO date_of_hire;

This command will rename the hire_date column to date_of_hire.

Step 7: Dropping a Column

This step involves dropping the email column from the employees table.

ALTER TABLE employees
DROP COLUMN email;

This command will remove the email column from the employees table.

Step 8: Renaming the Table

This step involves renaming the employees table to staff.

ALTER TABLE employees
RENAME TO staff;

This command will rename the employees table to staff.