Cómo Modificar Tablas en SQL con ALTER TABLE

Do you know what to do when you need to add, modify or delete a column in an existing SQL table? The ALTER TABLE command is an essential SQL tool that allows you to make these adjustments safely and clearly. Learn how to use add column, modify column, drop column, rename column and even rename an entire table.

How to add a new column in an existing table?

To add a new column in a SQL table, we use the basic syntax:

ALTER  TABLE tableName ADD COLUMN columnName dataType;

For example, to add a price column:

ALTER  TABLE test ADD COLUMN price  FLOAT;

How to specify the position of the added column?

You can place your new column at the beginning using FIRST, or after a specific column with AFTER:

ALTER  TABLE test ADD COLUMN price  FLOAT FIRST;ALTER  TABLE test ADD COLUMN price  FLOAT AFTER quantity;

How to remove columns from a table?

If you need to remove a column completely:

ALTER  TABLE test  DROP COLUMN price;

It is important to check well before executing this command, as you will lose all the data in that column.

How to modify existing columns?

When you need to change the data type or properties of a column:

ALTER  TABLE test MODIFY COLUMN price DECIMAL(10,3) NOT  NULL;

In this case, the price column will change from FLOAT to DECIMAL with 10 digits in total; 3 of them after the decimal point.

What precautions should be taken when modifying columns in production?

Changes to columns with existing information should be made with care. Make sure that the new data type is compatible with previously stored data.

Useful recommendations:

• Do pre-testing to check for possible errors.
• Performpeer reviews before executing changes in production.

How to rename columns and tables?

Renaming an existing column is accomplished with:

ALTER  TABLE test  RENAME COLUMN price TO prices;

Renaming the entire table is also easy:

ALTER  TABLE test  RENAME TO tests;

These changes do not affect the internal data, only the name of the structure.

How to adjust specific column attributes?

If you are looking to modify certain attributes, such as a default value, use modify column again keeping the parameters you do not want to change and including your additions:

ALTER  TABLE tests MODIFY COLUMN prices DECIMAL(10,3) NOT  NULL DEFAULT 10;

If you want to remove the default value, make sure the settings are congruent:

ALTER  TABLE tests MODIFY COLUMN prices DECIMAL(10,3) DEFAULT  NULL;

These examples will help you to keep your database tidy and clear. Share your experience or doubts in the comments!