Working With Alter Command in SQL

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

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

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.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore 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

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.

Learn From The Best Mentors in the Industry!

Automation Testing Masters ProgramExplore Program

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

Learn 15+ In-Demand Tools and Skills!

Automation Testing Masters ProgramExplore Program

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.