The ALTER command in SQL is used to make changes to a table, view, or the entire database. We can add, modify, and drop constraints, columns, and indexes using the ALTER command in SQL. It is not a part of the CRUD operations, but it is an essential part of DDL commands and can be used with multiple Relational Database Management Systems (RDBMS). For instance, we can use the ALTER command in SQL with SQL Server, MySQL, Oracle, and several other RDBMS.

PCP in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
PCP in Business Analysis

Examples of How to Use ALTER Command in SQL

In this section, we will be looking at several examples where we can use the ALTER command. For this article, we have used the Oracle database server. However, the syntax for Oracle, SQL Server, and many other RDBMS is the same.

Before starting with the examples, we will create two tables Employees and Salary. The Employees table will have the E_ID, E_Name, E_Job_Title, E_Gender, and E_Age columns. On the other hand, the Salary table will have the E_Sal_ID, E_Base_Salary, and E_Incentives columns. We will then also use the INSERT INTO command to add values to the tables.

CREATE TABLE Employees(

    E_ID NUMBER,

    E_Name VARCHAR(20),

    E_Job_Title VARCHAR(20),

    E_Gender VARCHAR(1),

    E_Age NUMBER

);

INSERT INTO Employees VALUES (1, 'George', 'Sales Representative', 'M', 23);

INSERT INTO Employees VALUES (2, 'Rachel', 'Manager', 'F', 35);

INSERT INTO Employees VALUES (3, 'David', 'Business Development', 'M', 26);

INSERT INTO Employees VALUES (4, 'Monica', 'Sales Representative', 'F', 21);

SELECT * FROM Employees;

Output

Alter_Command_in_SQL_1.

Free Course: Introduction to SQL

Learn MySQL, PostgreSQL and SQL ServerEnroll Now
Free Course: Introduction to SQL

CREATE TABLE Salary(

    E_Sal_ID NUMBER,

    E_Base_Salary NUMBER,

    E_Incentives NUMBER,

    E_ID NUMBER

);

INSERT INTO Salary VALUES(101, 15000, 2500, 1);

INSERT INTO Salary VALUES(201, 35000, 7500, 2);

INSERT INTO Salary VALUES(301, 25000, 5000, 3);

INSERT INTO Salary VALUES(401, 17000, 2500, 4);

SELECT * FROM Salary;

Output

Alter_Command_in_SQL_2

Now that we are done with both our tables, it's time to start with the examples to use the ALTER command in SQL.

Adding a New Column Using the ALTER Command

We can add a new column to an existing table using the ALTER command in SQL. In this example, we will add a new column E_Total in the Salary table.

ALTER TABLE Salary

ADD E_Total NUMBER;

SELECT * FROM Salary;

Output

Alter_Command_in_SQL_3

As you can see in the output, the ALTER command will add the column and assign the default value NULL to all the rows in that column.

Modifying an Existing Column With the ALTER Command in SQL

In this example, we will modify the name of the E_Total column that we just created in the previous section.

ALTER TABLE Salary

RENAME COLUMN E_Total TO E_Total_Salary;

SELECT * FROM Salary;

Output

Alter_Command_in_SQL_4.

Dropping an Existing Column With the ALTER Command

We can drop any existing column from a table with the ALTER Command in SQL. Here’s an example where we will drop the E_Total_Salary column.

ALTER TABLE Salary

DROP COLUMN E_Total_Salary;

SELECT * FROM Salary;

Output

Alter_Command_in_SQL_5.

As you can see in the output, the column that we added and renamed is now gone as we used the ALTER command to drop it.

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

Adding Primary Key Constraint Using the ALTER Command in SQL

We will add a primary key constraint to the E_ID column of our Employees table for this example.

ALTER TABLE Employees

ADD CONSTRAINT pk_Employees_E_ID

PRIMARY KEY (E_ID);

Output

Alter_Command_in_SQL_6

To check this, let’s try to include a row with a duplicate E_ID and see whether the server accepts it or throws the output as expected with a primary key.

INSERT INTO Employees VALUES (4, 'Chris', 'Sales Representative', 'M', 22);

Output

Alter_Command_in_SQL_7

Dropping Primary Key Constraint With the ALTER Command in SQL

In this example, we will drop the primary key we created in the previous example.

ALTER TABLE Employees

DROP CONSTRAINT pk_Employees_E_ID;

Output

Alter_Command_in_SQL_6

Adding a Foreign Key Constraint Using the ALTER Command

Suppose we want to add a foreign key constraint to create a relationship between two tables; we can also do that with the help of the ALTER command in SQL. Here’s an example to add a foreign key in the Salary table for the E_ID column that refers to the E_ID column of the Employees table.

ALTER TABLE Salary

ADD CONSTRAINT fk_Salary_E_ID FOREIGN KEY (E_ID) REFERENCES Employees (E_ID);

Output

Alter_Command_in_SQL_6

The table altered comment confirms that the said foreign key was created.

Free Course: Business Analysis Basics

Master the Fundamentals of Business AnalysisEnroll Now
Free Course: Business Analysis Basics

Dropping a Foreign Key Constraint With the ALTER Command in SQL

For this example, we will drop the very foreign key fk_Salary_E_ID that we created in the previous section.

ALTER TABLE Salary

DROP CONSTRAINT fk_Salary_E_ID;

Output

Alter_Command_in_SQL_6

Adding a Unique Key Constraint Using the ALTER Command

Suppose we want to add a UNIQUE KEY constraint to the E_Sal_ID column of the Salary table so that there are no two employees with a similar ID; we can do that with the ALTER command in SQL. Here’s how we can do it.

ALTER TABLE Salary

ADD CONSTRAINT uk_Salary_E_Sal_ID UNIQUE (E_Sal_ID);

Output

Alter_Command_in_SQL_6

Dropping a Unique Key Constraint With the ALTER Command in SQL

ALTER TABLE Salary

DROP CONSTRAINT uk_Salary_E_Sal_ID;

Output

Alter_Command_in_SQL_6

Adding a Check Constraint Using the ALTER Command

In this example, we will create a check constraint for the E_Base_Salary column to restrict any addition of rows with the base salary below 10,000.

ALTER TABLE Salary

ADD CONSTRAINT c_Salary_E_Base_Salary CHECK (E_Base_Salary>=10000);

Output

Alter_Command_in_SQL_6

Dropping a Check Constraint With the ALTER Command in SQL

In this example, we will drop the check constraint on the E_Base_Salary that we had created in the previous section.

ALTER TABLE Salary

DROP CONSTRAINT c_Salary_E_Base_Salary;

Output

Alter_Command_in_SQL_6

Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!

Conclusion

In this article, you learned everything about the ALTER command in SQL, along with various examples. You can use the ALTER command to change many things in a table of a database. With the understanding of this command, you can now try using it with your own tables and databases. Learning and understanding the use of ALTER commands is essential to manage vast databases. However, it is just one of the many fundamental concepts. If you want to learn the others, you can take Simplilearn’s SQL Certification Training Course. The course will get you acquainted with all the essential basic and advanced concepts of SQL database management, thereby helping you excel in the field.

About the Author

Shruti MShruti M

Shruti is an engineer and a technophile. She works on several trending technologies. Her hobbies include reading, dancing and learning new languages. Currently, she is learning the Japanese language.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.
  • *According to Simplilearn survey conducted and subject to terms & conditions with Ernst & Young LLP (EY) as Process Advisors