SQL ALTER TABLE Assertion
Introduction
ALTER command is among the most necessary SQL capabilities to know for database manipulation and administration. This Information Definition Language (DDL) command can alter present tables by including, eradicating, or updating columns, constraints, and indexes. We’ll have a look at other ways to make use of the ALTER command to focus on its flexibility. As well as, ALTER TABLE statements in SQL could also be used to vary the information sort of an present column to match new knowledge wants.
When you’re simply beginning out to discover SQL, right here’s a newbie’s information that can assist you: SQL For Information Science: A Newbie Information
Overview
- Be taught the aim and performance of the SQL ALTER command.
- Perceive how and the place to make use of SQL’s ALTER TABLE statements.
- Discover numerous eventualities and examples demonstrating the usage of this SQL perform.
Purposes of ALTER Command
The ALTER command in SQL can be utilized for a number of functions. Listed here are some frequent functions:
Pattern Desk
Right here’s a pattern desk on which we can be testing out numerous ALTER TABLE statements.
Including a New Column
So as to add a brand new column in our Worker desk, you should utilize the ADD COLUMN assertion.
ALTER TABLE staff
ADD COLUMN birth_date DATE;
SELECT emp_id, birth_date from Workers WHERE emp_id BETWEEN 1 AND 5;
This provides a brand new column named `birth_date` of sort DATE within the desk, however the values are empty as we simply added a brand new column in Workers.
Modifying an Current Column
To switch an present column, use the MODIFY or ALTER COLUMN clause.
ALTER TABLE staff
MODIFY COLUMN birth_date DATETIME;The `birth_date` column within the `staff` desk modifications from DATE to DATETIME sort. Equally we will change different knowledge varieties as properly for different columns.
Dropping a Column
To take away an present column out of your desk, use the DROP COLUMN in SQL.
ALTER TABLE staff
DROP COLUMN birth_date;This SQL question removes the birth_date column from the workers desk.
Including Constraints
The ALTER command’s makes use of prolong to including constraints to a desk like including a main key, overseas key, or distinctive constraint.
ALTER TABLE staff
ADD CONSTRAINT pk_employee_id PRIMARY KEY (emp_id);It will add a main key constraint named `pk_employee_id` on the `emp_id` column of the `staff` desk.
Eradicating Constraints
We use the DROP CONSTRAINT to take away a constraint from the desk.
ALTER TABLE staff
DROP CONSTRAINT pk_employee_id;It will take away the first key constraint (pk_employee_id) from the Workers desk.
Renaming a Desk
The ALTER command may rename a desk.
ALTER TABLE staff
RENAME TO employees;It will rename the `staff` desk to `employees`.
Conclusion
DDL’s ALTER command could be very efficient for managing and altering database desk buildings. Customers can add, change, or delete columns and constraints, offering the flexibleness required for adaptable database design and administration. This command makes it straightforward to replace database buildings as wants change.
Be taught Extra: SQL: A Full Fledged Information from Fundamentals to Superior Stage
Often Requested Questions
A. Sure you should utilize ALTER for this use case. Right here’s an instance:ALTER TABLE staff
MODIFY COLUMN employee_id VARCHAR(10);
A. You may add a default worth to an present column utilizing the ALTER command by utilizing SET DEFAULT. Right here’s an instance:ALTER TABLE staff
ALTER COLUMN employee_id SET DEFAULT 'UNKNOWN';
A. The ALTER TABLE and DROP COLUMN instructions can be utilized to drop a column. Right here’s an instance of dropping department_id from the workers desk:ALTER TABLE staff
DROP COLUMN department_id;
