Altering Tables in SQL

Altering tables in SQL is a crucial operation that allows you to modify the structure of an existing database table without losing any of the data it contains. This chapter will guide you through the basics of using the ALTER TABLE command, advancing to more complex scenarios with examples and explanations.

ALTER TABLE Statement

The ALTER TABLE statement in SQL is used to make changes to the structure of an existing table. These changes could involve adding, deleting, or modifying columns, changing data types, or renaming the table itself.

Syntax:

				
					ALTER TABLE table_name
MODIFY column_name column_type;

Adding Columns to a Table

Adding new columns to an existing table is one of the most common uses of the ALTER TABLE command. The new column is automatically populated with NULL values unless specified otherwise.

Syntax:

				
					ALTER TABLE table_name
ADD column_name column_type;

Example:

				
					-- Adding a new column "email" of type VARCHAR to the "employees" table
ALTER TABLE employees
ADD email VARCHAR(100);

Explanation:

Here, a new column email is added to the employees table, allowing you to store email addresses for each employee. The new column will be populated with NULL values for all existing rows.

Dropping Columns from a Table

The ALTER TABLE statement also allows you to remove an unwanted column from a table. Dropping a column is a permanent action, meaning that the column and its data will be permanently deleted.

Syntax:

				
					ALTER TABLE table_name
DROP COLUMN column_name;

Example:

				
					-- Dropping the "address" column from the "employees" table
ALTER TABLE employees
DROP COLUMN address;

Explanation:

In this case, the address column is removed from the employees table. Be cautious when dropping columns, as any data stored in the column will be lost.

Modifying Columns in a Table

You can use the ALTER TABLE command to modify the attributes of a column, such as changing its data type, renaming the column, or altering its size.

Syntax for Modifying Column:

				
					ALTER TABLE table_name
MODIFY column_name new_column_type;

Example:

				
					-- Modifying the "phone" column to hold up to 15 characters
ALTER TABLE employees
MODIFY phone VARCHAR(15);

Explanation:

Here, the phone column in the employees table is modified to hold a maximum of 15 characters, allowing for longer phone numbers.

Renaming Columns:

				
					ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

Example:

				
					-- Renaming "emp_id" column to "employee_id"
ALTER TABLE employees
RENAME COLUMN emp_id TO employee_id;

Explanation:

The emp_id column is renamed to employee_id for better readability.

Renaming a Table

If you need to rename an entire table, you can use the ALTER TABLE statement to achieve this.

Syntax:

				
					ALTER TABLE old_table_name
RENAME TO new_table_name;

Example:

				
					-- Renaming the "employees" table to "staff"
ALTER TABLE employees
RENAME TO staff;

Explanation:

The entire employees table is renamed to staff. All data remains intact, but the table is now referenced by the new name.

Changing Column Constraints

You can add or modify constraints such as NOT NULL, DEFAULT, UNIQUE, or CHECK using the ALTER TABLE command. This allows you to enforce data integrity and validation rules.

Adding NOT NULL Constraint:

				
					ALTER TABLE employees
MODIFY email VARCHAR(100) NOT NULL;

Explanation:

This statement modifies the email column to ensure that it cannot hold NULL values.

Adding a DEFAULT Value:

				
					ALTER TABLE employees
MODIFY status VARCHAR(20) DEFAULT 'Active';

Explanation:

A default value of ‘Active’ is added to the status column, so any new records will automatically have this value unless explicitly set.

Adding and Dropping Table Constraints

Constraints can be added to columns to maintain the integrity of the data. Examples include adding PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints.

Adding a Primary Key:

				
					ALTER TABLE employees
ADD PRIMARY KEY (employee_id);

Explanation:

Here, the employee_id column is set as the primary key, meaning it will uniquely identify each row in the table.

Dropping a Constraint:

				
					ALTER TABLE employees
DROP CONSTRAINT employee_id_pk;

Explanation:

This statement drops the primary key constraint from the employee_id column.

Best Practices for Using ALTER TABLE

  • Back up Data: Before making any structural changes to a table, especially when dropping columns or modifying data types, it’s a good practice to back up your data.
  • Minimal Disruption: When modifying large tables, be mindful of the impact on performance. Try to make alterations during low-traffic times.
  • Caution with Dropping Columns: Once a column is dropped, the data is permanently lost. Always review the necessity before performing this action.

The ALTER TABLE statement in SQL provides flexibility to adjust the structure of your database tables as your needs evolve. Whether you're adding new columns, modifying existing ones, or enforcing constraints, ALTER TABLE empowers you to make structural changes without having to recreate your entire table. However, care must be taken when performing alterations, especially when dropping data or changing constraints. Happy coding !❤️

Table of Contents

Home
Roadmap
ATS Resume
Playground
Resources