home bytes tutorials sql alter table in sql

Course Outline

Creating Table in SQL

Alter Table in SQL

Difference Between DROP and TRUNCATE in SQL

Alter Table in SQL

Last Updated: 22nd September, 2024

SQL ALTER TABLE is an essential SQL command that database developers use to change the structure of a table. An SQL table structure might require modification as business needs evolve. Therefore, the ALTER TABLE statement is crucial when it comes to adding, modifying, or deleting columns from an existing table. In addition to these functionalities, this SQL command can be used to add and drop various constraints to an existing table. For instance, developers can add foreign key constraints to ensure referential integrity. The ALTER TABLE statement is also used to enable or disable constraints, rename tables, or set storage parameters for tables. Therefore, SQL ALTER TABLE is a versatile SQL command that can be used to make a wide range of modifications to an existing table in a database.

Why ALTER TABLE in SQL is Important in Database Management?

Database structures need to adapt as requirements change over time. A table might need additional columns, or existing columns may require alterations to reflect changes in the business logic or application design. The ALTER TABLE command provides flexibility, allowing developers and database administrators (DBAs) to evolve the database schema without causing significant disruption to applications using it. The ability to add, modify, or remove columns and constraints dynamically enables efficient schema management.

ALTER TABLE ADD Column Statement in SQL

Syntax:

ALTER TABLE table_name 
ADD COLUMN column_name datatype;

employee_id	first_name	last_name
1	John	Smith
2	Jane	Doe

ALTER TABLE Employees 
ADD COLUMN Salary INT;

employee_id	first_name	last_name	salary
1	John	Smith	25000
2	Jane	Doe	30000

This code adds a new column named Salary to the Employees table with a data type of INT (integer). This means that the values stored in the Salary column will be numbers.

This code adds a column called 'Salary' with an integer data type to the Employees table.

ALTER TABLE MODIFY Column Statement in SQL

Syntax:

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

Example:

ALTER TABLE Employees MODIFY COLUMN Salary DECIMAL(10,2);

employee_id	first_name	last_name	salary
1	John	Smith	25000.00
2	Jane	Doe	30000.00

This code is used to alter the table named Employees. It modifies the column named Salary to have a data type of DECIMAL(10,2). This means that the column will store numbers totaling 10 digits, with 2 digits after the decimal point.

This statement changes the data type of the email column in the customer's table to VARCHAR(255).

ALTER TABLE DROP Column Statement in SQL

Syntax:

ALTER TABLE table_name
DROP COLUMN column_name;

Example:

ALTER TABLE employees
DROP COLUMN salary;

employee_id	first_name	last_name
1	John	Smith
2	Jane	Doe

This statement will delete the "salary" column from the "employees" table.

ALTER TABLE RENAME Column Statement in SQL

Syntax:

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

Example:

ALTER TABLE employees RENAME COLUMN last_name TO surname;

employee_id	first_name	surname
1	John	Smith
2	Jane	Doe

This statement renames the column named 'lastname' to 'surname' in the table 'employees'.

Using ALTER TABLE for Constraints

SQL constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and NOT NULL are vital for maintaining data integrity. You can add, modify, or drop these constraints using ALTER TABLE.

Adding a Constraint

To enforce data rules in a table, you can use the ALTER TABLE command to add constraints like NOT NULL, CHECK, or DEFAULT.

Example:

ALTER TABLE Employees
ADD CONSTRAINT check_salary CHECK (Salary > 0);

This will add a CHECK constraint to ensure that no negative values are inserted into the Salary column.

After adding this constraint, if you try to insert a negative salary:

INSERT INTO Employees (employee_id, first_name, last_name, salary)
VALUES (3, 'Mike', 'Johnson', -100);

You would receive an error like:

ERROR: Check constraint "check_salary" violated for row.

Dropping a Constraint

Similarly, if a constraint is no longer needed, you can use ALTER TABLE to remove it.

Example:

ALTER TABLE Employees
DROP CONSTRAINT check_salary;

This will remove the CHECK constraint from the Salary column.

Once the constraint is dropped, you can now insert negative values into the Salary column. This allows:

INSERT INTO Employees (employee_id, first_name, last_name, salary)
VALUES (3, 'Mike', 'Johnson', -100);

Output:

employee_id	first_name	last_name	salary
1	John	Smith	25000
2	Jane	Doe	30000
3	Mike	Johnson	-100

Using ALTER TABLE for Index Management

You can also manage indexes with the ALTER TABLE statement. An index can be created or dropped as needed, optimizing how data is queried from the table.

Adding an Index

ALTER TABLE Employees
ADD INDEX idx_last_name (last_name);

This command creates an index on the last_name column, speeding up queries that filter by last_name.

Renaming a Table with ALTER TABLE

In some cases, renaming a table might be necessary due to changes in the business model or application logic. This can easily be done with ALTER TABLE.

Syntax:

ALTER TABLE old_table_name
RENAME TO new_table_name;

Example:

ALTER TABLE Employees
RENAME TO CompanyEmployees;

This renames the table from Employees to CompanyEmployees.

Performance Considerations When Using ALTER TABLE

While the ALTER TABLE command is powerful, you should be cautious when modifying large tables. Actions like adding or dropping columns can lock the table for extended periods, affecting performance. If the table contains a large amount of data, such operations may take time, during which other queries may be delayed. Indexes may also need to be recalculated, which can further slow down database operations.

Conclusion

This lesson clarifies the SQL Alter TABLE command, which is utilized to adjust the structure of an existing table in a database. The command can be utilized to add, adjust, or erase columns, include or drop constraints, rename tables, and set storage parameters. The document gives illustrations of the syntax for adding, adjusting, dropping, and renaming columns in a table.

Key Takeaways

Alter TABLE is utilized to alter the structure of an existing table.
Modify TABLE can be utilized to add, adjust, or drop columns, adjust column properties, or rename tables.
To include a column in a table, utilize the Include COLUMN clause.
To adjust the properties of an existing column, utilize the Alter COLUMN clause.
To drop a column from a table, utilize the DROP COLUMN clause.
To rename a table, utilize the RENAME TO clause.
Be beyond any doubt to check the information sort and estimate of the column sometime recently, including or altering it.
Be beyond any doubt to utilize the right sentence structure when utilizing the Modify TABLE explanation.

Quiz

1. What does the ALTER TABLE statement do?

Delete a table
Update the data in a table
Add, delete, or modify columns in a table
Create a new table

Answer: C) Add, delete, or modify columns in a table

2. What is the syntax for adding a column to an existing table?

ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name ADD column_name;
ALTER TABLE table_name INSERT column_name datatype;
ALTER TABLE table_name INSERT column_name;

Answer: A) ALTER TABLE table_name ADD column_name datatype;

3. What happens when you use the ALTER TABLE statement to modify the data type of a column?

The data type of the column is changed, and all the data in the column is converted to the new data type
The data type of the column is changed, but the data remains unchanged
The data type of the column is changed, and the column is deleted
The data type of the column is changed, and new data is added to the column

Answer: B) The data type of the column is changed but the data remains unchanged

4. What is the syntax for deleting a column from an existing table?

ALTER TABLE table_name DROP column_name;
ALTER TABLE table_name DELETE column_name;
ALTER TABLE table_name REMOVE column_name;
ALTER TABLE table_name DEL column_name;

Answer: A) ALTER TABLE table_name DROP column_name;

Module 3: DDL Commands