Riven
Riven
SQL ALTER TABLE statement
The ALTER TABLE statement is used in SQL to make changes to the structure of a table after it has been created. This can include adding, deleting, or modifying columns; adding constraints; and renaming tables or columns.
Basic Syntax
Here, alter_specification can be one of the following:
- ADD: To add new columns or constraints.
- DROP: To remove columns or constraints.
- MODIFY: To change the data type or attributes of existing columns.
- RENAME: To rename the table or its columns.
Detailed Operations
1. Adding Columns
To add a new column to an existing table, you use the ADD keyword.
Example: Adding a New Column
Suppose we have a table called employees and we want to add a column for date_of_birth.
ALTER TABLE employees
ADD date_of_birth DATE;
This command adds a date_of_birth column of type DATE to the employees table.
Multiple Columns Example
You can add multiple columns in a single ALTER TABLE statement:
ALTER TABLE employees
ADD (
phone_number VARCHAR(15),
hire_date DATE
);
2. Dropping Columns
If you need to remove a column from a table, you use the DROP keyword.
Example: Dropping a Column
Let’s say we no longer need the phone_number column in the employees table:
ALTER TABLE employees
DROP COLUMN phone_number;
Important Consideration: Be careful when dropping columns, as this action cannot be undone and will result in the loss of any data stored in that column.
3. Modifying Columns
To change an existing column’s data type or constraints, use the MODIFY (or ALTER COLUMN in some databases) keyword.
Example: Modifying a Column
Suppose we want to change the data type of the phone_number column to accommodate more characters:
ALTER TABLE employees
MODIFY phone_number VARCHAR(20);
Changing Constraints Example
You can also modify constraints. For instance, if you want to change first_name to be NOT NULL:
ALTER TABLE employees
MODIFY first_name VARCHAR(50) NOT NULL;
4. Renaming Columns
To rename an existing column, the RENAME keyword is used.
Example: Renaming a Column
If we decide to rename the hire_date column to employment_date, we can do so like this:
ALTER TABLE employees
RENAME COLUMN hire_date TO employment_date;
5. Renaming a Table
You can also rename the entire table using the RENAME keyword.
Example: Renaming a Table
Let’s say we want to rename the employees table to staff:
ALTER TABLE employees
RENAME TO staff;
Adding Constraints
Constraints are rules enforced on data columns. Common constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT.
1. Adding a Primary Key Constraint
You can define a primary key to ensure that the values in a column (or a set of columns) are unique and not null.
Example: Adding a Primary Key
Assuming the employees table has an employee_id column:
ALTER TABLE employees
ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);
2. Adding a Foreign Key Constraint
A foreign key constraint is used to link two tables together.
Example: Adding a Foreign Key
Let’s say we have a departments table and want to link it with the employees table via a department_id column.
ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id) REFERENCES departments(department_id);
3. Adding a Unique Constraint
To ensure that all values in a column are unique, you can add a unique constraint.
Example: Adding a Unique Constraint
If you want to ensure that each email in the employees table is unique:
ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);
4. Adding a Check Constraint
A check constraint is used to limit the values that can be placed in a column.
Example: Adding a Check Constraint
If you want to ensure that the salary of employees is always greater than zero:
ALTER TABLE employees
ADD CONSTRAINT check_salary CHECK (salary > 0);
5. Adding a Default Constraint
You can set a default value for a column that will be used if no value is specified during an insert operation.
Example: Adding a Default Constraint
If you want the status column to default to ‘active’:
ALTER TABLE employees
ADD CONSTRAINT default_status DEFAULT 'active' FOR status;
Dropping Constraints
You may also need to drop constraints from your tables.
Example: Dropping a Constraint
To drop a foreign key constraint:
ALTER TABLE employees
DROP CONSTRAINT fk_department;
Considerations When Altering Tables
- Data Integrity: Always ensure that the changes you make do not violate existing data integrity.
- Backups: It’s a good practice to back up your data before performing significant alterations.
- Downtime: Some alterations can require downtime, especially for large tables. Plan accordingly.
- Database Specifics: The exact syntax and capabilities of
ALTER TABLEcan vary between different SQL databases (e.g., MySQL, PostgreSQL, SQL Server, Oracle). Always consult the specific documentation for your database.