MySQL

The ALTER TABLE statement in MySQL is used to modify the structure of an existing table without losing any data. This includes adding, modifying, or dropping columns, as well as renaming columns or tables and adding constraints. Modifying tables is a common task as application requirements evolve.

Below is a guide on how to use the ALTER TABLE command, with practical examples for a company database.

Common Uses of ALTER TABLE

1. Add Columns
   • Use ADD COLUMN to add new fields to an existing table.
2. Modify Columns
   • Use MODIFY COLUMN to change the data type or constraints of an existing column.
3. Drop Columns
   • Use DROP COLUMN to remove unnecessary columns.
4. Rename Columns
   • Use CHANGE COLUMN to rename a column or change its definition.
5. Add or Drop Constraints
   • Use ADD CONSTRAINT or DROP CONSTRAINT to manage table constraints.

Code Samples

1. Add a New Column to the employees Table

   ALTER TABLE employees
   ADD COLUMN phone_number VARCHAR(20);

   • Adds a new column phone_number to the employees table to store the employee's contact number.
2. Modify an Existing Column in the departments Table

   ALTER TABLE departments
   MODIFY COLUMN location VARCHAR(150) NOT NULL;

   • Changes the location column to be of type VARCHAR(150) and sets it as NOT NULL.
3. Drop a Column from the branches Table

   ALTER TABLE branches
   DROP COLUMN postal_code;

   • Removes the postal_code column from the branches table as it is no longer needed.
4. Rename a Column in the company Table

   ALTER TABLE company
   CHANGE COLUMN founded established DATE;

   • Renames the founded column to established in the company table, keeping the data type as DATE.
5. Add a Foreign Key Constraint to the employees Table

   ALTER TABLE employees
   ADD CONSTRAINT fk_department
   FOREIGN KEY (department_id) REFERENCES departments(department_id);

   • Adds a foreign key constraint to the department_id column in the employees table, linking it to the department_id in the departments table.

By using the ALTER TABLE command with these examples, beginners can easily modify their table structures as needed. Adjust the commands based on your specific requirements and database design.

To gain complete access, login with gmail or outlook, no need of signup, click here