Alter Table

How To Use Alter Table Command In SQL

Introduction:
In this article, you will learn what alter command is and how to use alter table command in SQL.

We use this command for Insert, Drop or Modify columns in the current table and Insert or Drop constraint also in the current table.

Syntax:

We have some type of statements for alter table like:

• Add a new column to the table:

In this statement, we can add new columns to our SQL tables.

The basic syntax of Add new column is as follows:

alter table table_name add column_name datatype;

Example:

Here, we have Employee table to apply some statement:

Step 1: We write the query to Add a new column.

Now, select and execute the query.

Step 2: The output of this query is:

• Drop Column in the table:

In this statement, we can delete the columns in our SQL tables.

The basic syntax of drop column is as follows:

alter table table_name drop column column_name;

Example:

Step 1: First, we write the query to drop a column.

Now, select and execute the query.

Step 2: The output of this query is:

• Change the Data Type of a column in a table:

In this statement, we can change our fields datatype in SQL tables.

The basic syntax to change the datatype of the column is as follows:

alter table table_name modify column column_name datatype;

• Add a Not Null constraint to a column in a table:

In this statement, we can add the not null constraint in our SQL tables.

The basic syntax of Add Not Null constraint is as follows:

alter table table_name modify column_name datatype not null;

• Add Unique Constraint to a table:

In this statement, we can add the unique constraint in our SQL tables.

The basic syntax of Add Unique constraint is as follows:

alter tabletable_name add constraint myuniqueconstraint unique(column1, column2...);

• Add Check Constraint to a table:

In this statement, we can add check constraint in our SQL tables.

The basic syntax of Add Check constraint on the column is as follows:

alter table table_name add constraint myuniqueconstraint check (condition);

• Add Primary Key constraint to a table:

In this statement, we add primary key in our SQL tables.

The basic syntax of Add Primary key constraint is as follows:

alter table table_name add constraint myprimarykey primary key (column1, column2...);

• Drop Constraint from a table:

In this statement, we can drop the constraint in our SQL tables.

The basic syntax of Drop constraint is as follows:

alter table table_name drop constraint myuniqueconstraint;

• Drop Primary Key constraint from a table:

In this statement, we can drop the Primary key in our SQL tables.

The basic syntax of Drop Primary key constraint is as follows:

alter table table_name drop constraint myprimarykey;

Summary:
Thus, we learned, alter command, which is used to insert, drop or modify the data and has also learned its use in SQL.