SQL Alter
What is Alter?
The ALTER used to modify the structure of an existing database, table, or index
It allows you to add, modify, or drop columns, constraints, and other elements of your database schema.
Syntax:
Alter Table (Add Column):
ALTER TABLE table_name
ADD COLUMN new_column_name datatype;
Alter Table (Modify Column):
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;
Alter Table (Drop Column):
ALTER TABLE table_name
DROP COLUMN column_name;
Other Alter Statements:
Add Primary Key:
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);
Drop Primary Key:
ALTER TABLE table_name
DROP PRIMARY KEY;
Add Foreign Key:
ALTER TABLE table_name
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (column_name)
REFERENCES referenced_table(referenced_column);
Drop Foreign Key:
ALTER TABLE table_name
DROP FOREIGN KEY fk_constraint_name;
Examples:
Assuming we have a table named employees with columns employee_id, first_name, last_name, and salary.
Example 1: Add a New Column
ALTER TABLE employees
ADD COLUMN department VARCHAR(50);
This adds a new column named department to the employees table.
Example 2: Modify Data Type of a Column
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(10, 2);
This changes the data type of the salary column to DECIMAL(10, 2).
Example 3: Drop a Column
ALTER TABLE employees
DROP COLUMN department;
This removes the department column from the employees table.
Example 4: Add Primary Key
ALTER TABLE employees
ADD PRIMARY KEY (employee_id);
This adds a primary key constraint to the employee_id column.
Example 5: Add Foreign Key
ALTER TABLE orders
ADD CONSTRAINT fk_employee_id
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id);
This adds a foreign key constraint to the employee_id column in the orders table, referencing the employee_id column in the employees table.
Tips:
- Be careful when modifying or dropping columns, as it may result in data loss.
- Ensure that the new data type is compatible with the existing data when modifying columns.
- Use meaningful constraint names for better maintainability.