SQL ALTER TABLE Assertion

0
52


Introduction

ALTER command is without doubt one of the most essential SQL capabilities to know for database manipulation and administration. This Knowledge Definition Language (DDL) command can alter current tables by including, eradicating, or updating columns, constraints, and indexes. We’ll take a look at alternative ways to make use of the ALTER command to spotlight its flexibility. As well as, ALTER TABLE statements in SQL could also be used to alter the information kind of an current column to match new information wants.

When you’re simply beginning out to discover SQL, right here’s a newbie’s information that will help you: SQL For Knowledge Science: A Newbie Information

Overview

  • Study 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 using this SQL operate.

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 will likely be testing out numerous ALTER TABLE statements.

SQL ALTER command

Including a New Column

So as to add a brand new column in our Worker desk, you should use the ADD COLUMN assertion.

ALTER TABLE workers
ADD COLUMN birth_date DATE;
SELECT emp_id, birth_date from Workers WHERE emp_id BETWEEN 1 AND 5;
SQL ALTER TABLE Statement

This provides a brand new column named `birth_date` of kind DATE within the desk, however the values are empty as we simply added a brand new column in Workers.

Modifying an Present Column

To switch an current column, use the MODIFY or ALTER COLUMN clause.

ALTER TABLE workers
MODIFY COLUMN birth_date DATETIME;

The `birth_date` column within the `workers` desk adjustments from DATE to DATETIME kind. Equally we are able to change different information sorts as effectively for different columns.

Dropping a Column

To take away an current column out of your desk, use the DROP COLUMN in SQL.

ALTER TABLE workers
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 lengthen to including constraints to a desk like including a major key, international key, or distinctive constraint.

ALTER TABLE workers
ADD CONSTRAINT pk_employee_id PRIMARY KEY (emp_id);

This may add a major key constraint named `pk_employee_id` on the `emp_id` column of the `workers` desk.

Eradicating Constraints

We use the DROP CONSTRAINT to take away a constraint from the desk.

ALTER TABLE workers
DROP CONSTRAINT pk_employee_id;

This may take away the first key constraint (pk_employee_id) from the Workers desk.

Renaming a Desk

The ALTER command can even rename a desk.

ALTER TABLE workers
RENAME TO employees;

This may rename the `workers` desk to `employees`.

Conclusion

DDL’s ALTER command may 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.

Study Extra: SQL: A Full Fledged Information from Fundamentals to Superior Stage

Ceaselessly Requested Questions

Q1. Does the ALTER command help altering an outdated column’s information kind?

A. Sure you should use ALTER for this use case. Right here’s an instance:
ALTER TABLE workers
MODIFY COLUMN employee_id VARCHAR(10);

Q2. The right way to set a default worth to a column utilizing ALTER?

A. You possibly can add a default worth to an current column utilizing the ALTER command through the use of SET DEFAULT. Right here’s an instance:
ALTER TABLE workers
ALTER COLUMN employee_id SET DEFAULT 'UNKNOWN';

Q3. Can SQL I take away a column from a desk?

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 workers
DROP COLUMN department_id;