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.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program
Want a Top Software Development Job? Start Here!

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.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program
Want a Top Software Development Job? Start Here!

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.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program
Want a Top Software Development Job? Start Here!

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.

Learn From The Best Mentors in the Industry!

Automation Testing Masters ProgramExplore Program
Learn From The Best Mentors in the Industry!

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

Learn 15+ In-Demand Tools and Skills!

Automation Testing Masters ProgramExplore Program
Learn 15+ In-Demand Tools and Skills!

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.

Data Science & Business Analytics Courses Duration and Fees

Data Science & Business Analytics programs typically range from a few weeks to several months, with fees varying based on program and institution.

Program NameDurationFees
Caltech Post Graduate Program in Data Science

Cohort Starts: 29 May, 2024

11 Months$ 4,500
Post Graduate Program in Data Engineering

Cohort Starts: 4 Jun, 2024

8 Months$ 3,850
Data Analytics Bootcamp

Cohort Starts: 11 Jun, 2024

6 Months$ 8,500
Post Graduate Program in Data Analytics

Cohort Starts: 17 Jun, 2024

8 Months$ 3,500
Applied AI & Data Science

Cohort Starts: 18 Jun, 2024

3 Months$ 2,624
Post Graduate Program in Data Science

Cohort Starts: 19 Jun, 2024

11 Months$ 3,800
Data Scientist11 Months$ 1,449
Data Analyst11 Months$ 1,449