ALTER TABLE operations in MySQL - Step-by-Step Execution

Concept Flow - ALTER TABLE operations

Start ALTER TABLE

↓

Parse command

↓

Check table exists?

No→Error: Table not found

Yes↓

Identify operation type

↓

Add column

↓

Drop column

↓

Modify column

↓

Rename column

↓

Rename table

↓

Execute operation

↓

Update table structure

↓

Finish ALTER TABLE

The ALTER TABLE command starts by parsing the input, checking if the table exists, then identifies the specific operation (add, drop, modify, rename), executes it, updates the table structure, and finishes.

Execution Sample

MySQL

ALTER TABLE employees
ADD COLUMN age INT;

This command adds a new column named 'age' of type INT to the 'employees' table.

Execution Table

Step	Action	Evaluation	Result
1	Start ALTER TABLE employees ADD COLUMN age INT	Parse command	Command parsed successfully
2	Check if table 'employees' exists	Table found	Proceed to operation
3	Identify operation type	ADD COLUMN detected	Prepare to add column
4	Check if column 'age' exists	Column does not exist	Safe to add
5	Add column 'age' INT to table structure	Column added	Table structure updated
6	Finish ALTER TABLE	Operation complete	Table altered successfully

💡 Operation stops after successfully adding the new column and updating the table structure.

Variable Tracker

Variable	Start	After Step 3	After Step 5	Final
table_structure	{id, name, salary}	{id, name, salary}	{id, name, salary, age}	{id, name, salary, age}
operation_type	N/A	ADD COLUMN	ADD COLUMN	ADD COLUMN
column_exists	N/A	False	False	False

Key Moments - 2 Insights

Why does the ALTER TABLE command check if the column already exists before adding?

Because adding a column with a name that already exists would cause an error. The check ensures safe modification as shown in step 4 of the execution_table.

What happens if the table does not exist?

The command stops immediately with an error 'Table not found' as shown in the concept_flow after the 'Check table exists?' step.

Visual Quiz - 3 Questions

Look at the execution_table, what is the operation_type after step 3?

A DROP COLUMN

B ADD COLUMN

C MODIFY COLUMN

D RENAME TABLE

💡 Hint

Check the 'operation_type' column in variable_tracker after Step 3.

At which step does the table structure get updated?

A Step 2

B Step 4

C Step 5

D Step 6

💡 Hint

Look for the step where 'Add column' action updates the table structure in execution_table.

If the column 'age' already existed, what would happen at step 4?

A Stop with an error

B Proceed to add the column anyway

C Rename the existing column

D Ignore the ADD COLUMN command

💡 Hint

Refer to key_moments about why the column existence check is important.

Concept Snapshot

ALTER TABLE syntax:
ALTER TABLE table_name
ADD|DROP|MODIFY|RENAME COLUMN ...;

Steps:
1. Parse command
2. Check table exists
3. Identify operation
4. Validate operation
5. Execute and update

Always check for existing columns or tables to avoid errors.

Full Transcript

The ALTER TABLE command modifies a table's structure. It starts by parsing the command and checking if the table exists. Then it identifies the operation type such as adding or dropping a column. Before adding a column, it checks if the column already exists to prevent errors. If all checks pass, it updates the table structure and finishes the operation successfully.

Practice

1. What does the ALTER TABLE statement do in MySQL?

easy

A. It changes the structure of an existing table without deleting data.

B. It deletes all data from a table permanently.

C. It creates a new table with specified columns.

D. It backs up the database automatically.

Solution

1. Step 1: Understand the purpose of ALTER TABLE

   The ALTER TABLE command is used to change the structure of an existing table, such as adding or removing columns, without deleting the data inside.

2. Step 2: Compare with other options

   Options A, B, and D describe different commands: creating tables, deleting data, or backing up, which are not the purpose of ALTER TABLE.

3. Final Answer:

   It changes the structure of an existing table without deleting data. -> Option A

4. Quick Check:

   ALTER TABLE modifies table structure [OK]

Hint: ALTER TABLE changes structure, not data content [OK]

Common Mistakes:

• Confusing ALTER TABLE with DELETE or DROP commands
• Thinking ALTER TABLE creates new tables
• Assuming ALTER TABLE backs up data

2. Which of the following is the correct syntax to add a new column named age of type INT to a table called users?

easy

A. ALTER TABLE users ADD age;

B. ALTER users ADD age INT;

C. ALTER TABLE users ADD COLUMN age INT;

D. ALTER TABLE ADD COLUMN age INT TO users;

Solution

1. Step 1: Recall correct ALTER TABLE syntax for adding columns

   The correct syntax is: ALTER TABLE table_name ADD COLUMN column_name datatype; The keyword COLUMN is optional but recommended.

2. Step 2: Check each option

   ALTER TABLE users ADD COLUMN age INT; matches the correct syntax. ALTER users ADD age INT; misses TABLE keyword. ALTER TABLE users ADD age; misses datatype. ALTER TABLE ADD COLUMN age INT TO users; has wrong order.

3. Final Answer:

   ALTER TABLE users ADD COLUMN age INT; -> Option C

4. Quick Check:

   ADD COLUMN syntax = ALTER TABLE users ADD COLUMN age INT; [OK]

Hint: Use ALTER TABLE table ADD COLUMN col datatype [OK]

Common Mistakes:

• Omitting TABLE keyword
• Forgetting datatype for new column
• Incorrect order of keywords

3. Given the table employees with columns id INT, name VARCHAR(50), what will be the result of this query?

ALTER TABLE employees DROP COLUMN name;

medium

A. The table employees will be deleted.

B. The data in the name column will be set to NULL but column remains.

C. A syntax error will occur because DROP COLUMN is invalid.

D. The column name will be removed from the table.

Solution

1. Step 1: Understand DROP COLUMN in ALTER TABLE

   Using DROP COLUMN removes the specified column and all its data from the table structure permanently.

2. Step 2: Analyze the query effect

   The query removes the 'name' column from 'employees'. It does not delete the table or cause syntax errors.

3. Final Answer:

   The column name will be removed from the table. -> Option D

4. Quick Check:

   DROP COLUMN removes column and data [OK]

Hint: DROP COLUMN deletes column, not table [OK]

Common Mistakes:

• Thinking DROP COLUMN deletes the whole table
• Assuming DROP COLUMN sets data to NULL only
• Believing DROP COLUMN causes syntax error

4. You run this command:

ALTER TABLE orders MODIFY COLUMN order_date VARCHAR(20);

But get an error. What is the likely cause?

medium

A. The MODIFY COLUMN syntax is incorrect in MySQL.

B. The column order_date does not exist in the table.

C. VARCHAR(20) is not a valid data type.

D. You cannot change a column's data type with ALTER TABLE.

Solution

1. Step 1: Check MODIFY COLUMN syntax validity

   MySQL supports MODIFY COLUMN to change a column's data type, so syntax is correct.

2. Step 2: Identify possible causes of error

   If the column does not exist, MySQL throws an error. VARCHAR(20) is valid, so the likely cause is the column missing.

3. Final Answer:

   The column order_date does not exist in the table. -> Option B

4. Quick Check:

   Nonexistent column causes MODIFY error [OK]

Hint: Check column exists before MODIFY COLUMN [OK]

Common Mistakes:

• Assuming MODIFY COLUMN syntax is wrong
• Thinking data type change is not allowed
• Ignoring column existence

5. You want to rename the column username to user_name in the accounts table and change its type to VARCHAR(100). Which command is correct?

hard

A. ALTER TABLE accounts CHANGE username user_name VARCHAR(100);

B. ALTER TABLE accounts RENAME COLUMN username TO user_name VARCHAR(100);

C. ALTER TABLE accounts MODIFY username TO user_name VARCHAR(100);

D. ALTER TABLE accounts RENAME username user_name VARCHAR(100);

Solution

1. Step 1: Understand renaming columns in MySQL

   MySQL uses CHANGE to rename a column and optionally change its type: CHANGE old_name new_name datatype.

2. Step 2: Evaluate each option

   ALTER TABLE accounts CHANGE username user_name VARCHAR(100); uses correct CHANGE syntax. ALTER TABLE accounts RENAME COLUMN username TO user_name VARCHAR(100); uses invalid RENAME COLUMN syntax for MySQL. Options C and D use incorrect keywords.

3. Final Answer:

   ALTER TABLE accounts CHANGE username user_name VARCHAR(100); -> Option A

4. Quick Check:

   CHANGE renames and modifies column [OK]

Hint: Use CHANGE old_col new_col datatype to rename and modify [OK]

Common Mistakes:

• Using RENAME COLUMN which is not supported in MySQL
• Confusing MODIFY with renaming
• Omitting datatype when using CHANGE