What can I do with SQL ALTER TABLE?

ALTER TABLE lets you add or drop columns, change or modify column data types, rename columns, add or drop indexes, and add or drop constraints — all without recreating the table.

Does ALTER TABLE work in all SQL databases?

Yes, ALTER TABLE is supported in all major SQL databases including MySQL, Oracle, SQL Server, Google BigQuery, SparkSQL, and HiveQL. However, the exact syntax for each operation varies by database.

Which ALTER TABLE operations are supported in SparkSQL and HiveQL?

SparkSQL and HiveQL support ALTER TABLE ADD COLUMN (to add new columns) and ALTER TABLE CHANGE COLUMN (to rename or change the data type of a column). Other operations like DROP COLUMN require different approaches in these platforms.

Will ALTER TABLE lock my table while it runs?

This depends on the database and operation. In MySQL (InnoDB), many ALTER TABLE operations use online DDL which minimizes locking. In older versions or for certain operations, the table may be locked for the duration of the change.

SQL ALTER TABLE

SQL > ALTER TABLE

Once a table is created in the database, there are many occasions where one may wish to change the structure of the table. In general, the SQL syntax for ALTER TABLE is,

SQL ALTER TABLE is the command used to modify an existing table's structure — adding, dropping, renaming, or changing columns, indexes, and constraints — without affecting the underlying data.

ALTER TABLE "table_name"
[alter specification];

[alter specification] is dependent on the type of alteration we wish to perform. We list a number of common changes below:

For all cases except Change Column, examples are provided for MySQL, Oracle, and SQL Server. SparkSQL and Hive SQL (HiveQL) only support Add Column and Change Column with ALTER TABLE.

Frequently Asked Questions

Q: What can I do with SQL ALTER TABLE?: A: ALTER TABLE lets you add or drop columns, change or modify column data types, rename columns, add or drop indexes, and add or drop constraints — all without recreating the table.
Q: Does ALTER TABLE work in all SQL databases?: A: Yes, ALTER TABLE is supported in all major SQL databases including MySQL, Oracle, SQL Server, Google BigQuery, SparkSQL, and HiveQL. However, the exact syntax for each operation varies by database.
Q: Which ALTER TABLE operations are supported in SparkSQL and HiveQL?: A: SparkSQL and HiveQL support ALTER TABLE ADD COLUMN (to add new columns) and ALTER TABLE CHANGE COLUMN (to rename or change the data type of a column). Other operations like DROP COLUMN require different approaches in these platforms.
Q: Will ALTER TABLE lock my table while it runs?: A: This depends on the database and operation. In MySQL (InnoDB), many ALTER TABLE operations use online DDL which minimizes locking. In older versions or for certain operations, the table may be locked for the duration of the change.

Next: SQL ADD COLUMN

This page was last updated on March 19, 2026.

AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.

SQL ALTER TABLE

Frequently Asked Questions

Related Topics

AdBlock Detected!