Learn SQL for Developers/

...

/

Table Modifications

Table Modifications

Learn to modify tables after creation in SQL.

We'll cover the following...

Imagine we have been managing the OnlineStore database for months, and our business requirements are evolving. We might need to record additional product details or remove outdated columns. This flexibility in adapting our table structures is essential for keeping our data relevant. 

Let's learn how to modify existing tables using the ALTER TABLE statement and how to remove entire tables with the DROP TABLE statement. Our aim is to:

  • Understand the purpose of ALTER TABLE.

  • Add and remove columns without losing other data.

  • Modify column data types to meet new requirements.

  • Drop entire tables and grasp the consequences of doing so.

Using ALTER TABLE statement

When our table designs no longer match current requirements, we rely on ALTER TABLE to introduce structural changes. This statement allows us to add new columns, remove columns we no longer need, and even change column data types so that our tables accurately reflect the data we want to store. Let's explore these one by one.

Adding columns

Adding a new column is often necessary when we need to store additional attributes. Suppose we want to add a ProductDescription column in the Products table to provide extra details about each product. We could do this with the ADD keyword as follows:

Press + to interact
-- Definition for the Products table
-- CREATE TABLE Products (
--     ProductID INT PRIMARY KEY  AUTO_INCREMENT,
--     ProductName VARCHAR(50) NOT NULL UNIQUE,
--     CategoryID INT,
--     Price DECIMAL(10, 2) NOT NULL,
--     Stock INT NOT NULL,
--     FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
-- );
-- ------------------------------------------------------------------------------
ALTER TABLE Products
ADD COLUMN ProductDescription VARCHAR(255);
DESCRIBE Products;

The command above adds a ProductDescription column at the end of the Products table. We can then update product entries to include relevant details without affecting other columns.

Dropping columns

Sometimes a column becomes ...