10 Examples of ALTER Table Command in SQL

In this SQL tutorial, you will learn how to use ALTER command in the table on the database. ALTER command is mainly used to add, modify and drop columns, indexes, and constraints on the table in relational databases e.g. MySQL, Oracle, Sybase, and SQL Server.  Though ALTER is not part of classical CRUD operation but it’s one of the important DDL commands. One of the most frequent uses of ALTER command in SQL is adding and removing indexes to improve the performance of SQL SELECT queries.

In this SQL and database tutorial, we will see different ways of using ALTER command. By using ALTER command we can add, edit, or modify tables, views, and databases. We can add a new column on the table, we can change the value of the column or we can rename the column also.

Btw, If you are new to SQL and don't understand fundamentals like COUNT() function or co-related sub-queries then I highly recommend you go through a comprehensive SQL course like The Complete SQL Bootcamp by Jose Portilla on Udemy. It's one of the best and also most affordable courses to learn SQL online.

10 ALTER command Example in SQL

Let’s see some of the most frequently used examples of ALTER command in SQL. In this section, we will see How to use ALTER command to add, modify, and drop existing columns in the table.

Example 1: How to add a column in an existing table using ALTER clause in SQL:

Suppose we have one table called student with field roll_number, stu_name, class, subject and we want to add one more column called marks-obtained, how will do using ALTER Command.

ALTER TABLE Student 
ADD marks_obtained Number (3);

When this field is added to the table it will contain NULL values by default. If you want to learn more about NULL in SQL, which you should then I highly recommend you to join a comprehensive SQL course like The Complete Oracle SQL Certification Course on Udemy. 

Example 2:  How to modify existing column using ALTER command in SQL:

Suppose now we want to modify the above-added field mark_obtained from number to decimal data type we can use modify column along with the alter command.

ALTER TABLE Student 
ALTER COLUMN marks_obtained DECIMAL (5, 2);

Example 3:  How to drop an existing column from the table using ALTER command in SQL:

Now if we want to drop some existing columns from the table we use the drop along with ALTER command

ALTER TABLE Student 
DROP column marks_obtained;

Example 4. How to drop Primary Key Constraint using ALTER command in database :

Suppose there is a PRIMARY KEY CONSTRAINT named 'pk_roll_num' for the column roll_number of the Student table and we want to drop this constraint

ALTER TABLE Student 
DROP CONSTRAINT 'pk_roll_num’;

Example 5. How to add primary key constraints using ALTER command in SQL:

Now if we want to add the primary key constraint in the table Student named 'pk_roll_num' for the column roll_number'. We do like this

ALTER TABLE Student 
ADD Constraint 'pk_roll_num 
PRIMARY KEY(roll_number)

If you want to learn more about essential SQL and database concepts like Primary key, Unique key, Foreign key then I also suggest you check out this Introductory Guide to SQL, an interactive SQL course by Educative, a text-based online learning platform that allows you to run SQL command on browser without installing and setting up any database on your machine. 

Example 6. How to drop foreign key Constraint using Alter command:

Suppose there is a foreign key constraint named 'fk_roll_num' for the column roll_number' of the 'Marks' table and we want to drop this constraint

ALTER TABLE Marks 
DROP CONSTRAINT 'fk_roll_num’;

Example 7: How to add foreign key Constraints using alter command

Now if we want to add a foreign key constraint in table Marks named 'fk_roll_num' for the column roll_number' .we do like this

ALTER TABLE Marks 
ADD Constraint 'fk_roll_num 
FOREIGN KEY (roll_number) REFERENCES Students (roll_number);

Example 8: How to add unique key Constraints using ALTER command

Now if we want to add a Unique key constraint in the table Student named ‘unique_roll_no ' for the column roll_number' .we do like this

ALTER TABLE Student 
ADD CONSTRAINT unique_roll_no 
UNIQUE (roll_Number);

Example 9: How to drop unique Key Constraint using ALTER command

Now if we want to drop unique a key constraint in table Student named ‘unique_roll_no ' for the column roll_number' .we do like this

ALTER TABLE Student 
DROP CONSTRAINT unique_roll_no);

Example 10: How to add check Constraint using ALTER command in SQL

The basic syntax for adding check constraints in a table is like this

ALTER TABLE table_name 
ADD CONSTRAINT MyUniqueConstraint 
CHECK (CONDITION);

This is all about ALTER command examples in SQL. We have seen 10 different examples of using ALTER command in SQL. ALTER is one of the most important while working in any database like MySQL, Oracle, or SQL Server, especially from the command line. Since we often need to add, modify and drop columns, indexes, and constraints in the table.

Other related SQL queries, Interview questions, and articles:

• 5 Courses to learn Database and SQL Better (courses)
• Difference between Self and Equi Join in SQL? (answer)
• 5 Free Courses to learn Oracle and SQL Server? (courses)
• Difference between clustered and non-clustered indexes in SQL? (answer)
• How to join three tables in one single SQL query (solution)
• Top 5 Websites to learn SQL online for FREE? (resource)
• 5 Advanced SQL books to level up your SQL skills (books)
• Write a SQL query to copy or backup a table in MySQL (solution)
• Top 5 Courses to learn MySQL Database for Beginners (Courses)
• 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
• How do you find the duplicate rows in a table on a database? (solution)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• Top 5 Courses to learn Microsoft SQL Server in-depth (courses)
• The real difference between WHERE and HAVING clause in SQL? (answer)
• 4 ways to find the Nth highest salary in SQL (solution)
• Top 5 Courses to learn PostgreSQL in-depth (courses)
• Difference between the Unique and Primary keys in the table? (answer)
• 5 Free Courses to learn Database and SQL (free courses)

Thanks for reading this article, if you find these SQL interview questions useful, then please share them with your friends and colleagues. If you have any questions or feedback, then please drop a note.

P. S. - If you are new to SQL and database and looking for a free online training/course to learn SQL from scratch, I suggest you joining  Introduction to Databases and SQL Querying [FREE] course on Udemy. It's one of the best free courses to learn SQL fundamentals like join, subquery, aggregate functions, window functions, grouping data, advanced filtering, and SQL query optimization.