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.
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
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
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
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
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
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.
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
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
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
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
The table altered comment confirms that the said foreign key was created.
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
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
Dropping a Unique Key Constraint With the ALTER Command in SQL
ALTER TABLE Salary
DROP CONSTRAINT uk_Salary_E_Sal_ID;
Output
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
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
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.