Prev Next

MySQL Alter Table

The ALTER TABLE statement is used to modify the structure of an existing table. We'll show how to alter the tamil_kings table, such as adding, modifying, or dropping columns.

Key Topics

1. Adding a Column

To add a new column, use:

ALTER TABLE tamil_kings ADD birthplace VARCHAR(100);

Updated Table Structure:

| id | name | reign_period | notable_contributions | birthplace | |----|----------------|------------------|----------------------|-------------------| | 1 | Raja Raja Chola| 985–1014 CE | Brihadeeswarar Temple| Thanjavur |

Code Explanation: This command adds a new column named birthplace to the tamil_kings table, allowing you to store the birthplace of each king.

2. Modifying a Column

To change the data type of an existing column, use:

ALTER TABLE tamil_kings MODIFY reign_period TEXT;

Updated Table Structure:

| id | name | reign_period | notable_contributions | birthplace | |----|----------------|------------------|----------------------|-------------------| | 1 | Raja Raja Chola| 985–1014 CE | Brihadeeswarar Temple| Thanjavur |

Code Explanation: This command modifies the reign_period column to use the TEXT data type, which is useful for longer text entries.

3. Dropping a Column

To remove an existing column, use:

ALTER TABLE tamil_kings DROP COLUMN birthplace;

Updated Table Structure:

| id | name | reign_period | notable_contributions | |----|----------------|------------------|----------------------| | 1 | Raja Raja Chola| 985–1014 CE | Brihadeeswarar Temple|

Code Explanation: This command drops the birthplace column from the tamil_kings table, permanently removing it and its data.

Best Practices

  • Back up your table before making any structural changes.
  • Ensure that modifying columns will not affect existing data in unintended ways.
  • Document all changes made to database structures.

Key Takeaways

  • The ALTER TABLE statement is used to modify existing table structures.
  • Common operations include adding, modifying, and dropping columns.
  • Always back up data before making structural changes to a table.
Prev Next