bytes
tutorials
sql
alter table in sql
Overview
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.
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'.
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
Quiz
1. What does the ALTER TABLE statement do?
Answer: C) Add, delete, or modify columns in a table
2. What is the syntax for adding a column to an existing table?
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?
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?
Answer: A) ALTER TABLE table_name DROP column_name;