SQL Alter Command

SQL Tutorial


[fblike]

SQL ALTER Command : Introduction

  • The SQL ALTER Command is an exciting command to use. It is used to ALTER or modify the overall structure of tables inside the database rather than altering the values of tables.
  • It means, alteration in rows or columns through add, delete or drop clause can be done. Let’s look at the syntax first and then we will try to make this clear with multiple examples.

 

SQL ALTER Command : Syntax

  • The syntax of ALTER Command varies depending upon the type of alteration the user wants. The syntax for each is illustrated below.
This image describes the four different types of sql alter command that are used in sql.

SQL ALTER Command : Types

 

SQL ALTER Command : Example

  • Consider the table of student_details over which all the above commands will be executed.

Table : student_details

 

This image describes the select query results.

 

Example-1 : Adding a new column of “Location” in student_details table.

Query : ALTER Table student_details ADD Location varchar(20);

Output :

This image describes the select query results.

 

Note : The values in column “Location” are left blank because had added the column to the table and not inserted the values in it.


Example-2 : Modifying data type of column “Name” in the student_details table.

Query : ALTER Table student_details MODIFY COLUMN Name char(10);

Output : You won’t see any change in view of table. But, while inserting new values, only character values will be inserted in the “Name” column.

 

This image describes the select query results.

 

Example-3 : Dropping a column “Marks” from student_details table.

Query : ALTER Table student_details DROP COLUMN Marks;

Output : 

 

This image describes the select query results.

 

Example-4 : Adding Primary Key in student_details table.

Query : ALTER Table student_details ADD CONSTRAINT C1 PRIMARY KEY (Roll_No);

Output :

 

This image describes the select query results.

 

Note : A Primary Key is an attribute of a table with the help of which each data record present in the table can be uniquely identified. To know more about, please refer KEYS IN DBMS.