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.

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

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

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

Output :

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.

   

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

Query : ALTER Table student_details DROP COLUMN Marks;

Output : 

 

Example-4 : Adding Primary Key in student_details table.

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

Output :

 

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.