Introduction to the ALTER TABLE Statement in SQL

In SQL, there are mainly three types of commands -

• DDL - which stands for Data Definition Language which comprises SQL commands used for defining the database which includes creating tables, providing specifications, modifying tables and so on. Examples of DDL include CREATE, DROP, ALTER, etc. The changes that are caused by issuing DDL commands cannot be rolled back.

• DML - which stands for Data Manipulation Language which lets you run select, insert, update and delete queries. The changes that are caused by issuing DML commands can be rolled back. Example - SELECT, INSERT, UPDATE and DELETE.

• DCL - which stands for Data Control Language which lets you specify rights, permissions and other controls of your database system.

In this tutorial, you will see the usage of the ALTER TABLE statement which is DDL in nature. Following contents are going to be covered in this tutorial -

• The need of the ALTER TABLE statement
• Creating a table in a PostgreSQL database
• Executing different ALTER TABLE statements in PostgreSQL

Note: The tutorial is not going to teach how to create a table, inserting data in a table, select data from a table in PostgreSQL. If you want a refresher on these topics, you can check this article.

The Need of the ALTER TABLE Statement

Specifications of a database may change with time. For example, you may need to add a column to a particular table, or you may need to add another primary key to a table. You may also need to change the data-type of a particular column in a table. You may not afford to drop a table and create it again from scratch. Alter Table statement lets you do all this very seamlessly. With Alter Table, you can -

• Add, modify, or drop a column
• Add or drop a constraint

You can also enable/disable constraints, but that is not in the scope of this tutorial.

You will see each of the above use-cases in this tutorial, but before you do so, you will need a table in PostgreSQL so that you can get started. In the next section, you will create a simple table.

Creating a table in a PostgreSQL database

You will create a table named student_details in PostgreSQL (using the pgAdmin utility) with the following columns and data-types -

• student_id (integer)
• student_full_name (character)
• student_year (integer)

You will start with this table comprising just three columns. Later, as you proceed through the tutorial, you will add more columns to this table and will alter the table in different ways. The following create query will get you a table created according to the above specifications -

CREATE TABLE student_details(
 student_id SERIAL,
 student_full_name VARCHAR (50),
 student_year VARCHAR (100)
);

Let's insert a few records to this table as well -

• INSERT INTO student_details(student_full_name, student_year) VALUES('John Doe','1st');
• INSERT INTO student_details(student_full_name, student_year) VALUES('Chris Villa','3rd');

As the data-type of student_id is set to SERIAL PostgreSQL will automatically increase the column value and the data-type is interpreted as integer by default by PostgreSQL.

The table student_details should look like this -

You now have a table set up in PostgreSQL. Let's now see a different application of Alter Table in the next section.

Executing different ALTER TABLE statements in PostgreSQL

In this section, you will see the usage of Alter Table statement in the following two ways as mentioned above -

• Add, modify, and drop a column
• Add and drop a constraint

Add, modify, or drop a column

Let's start by adding one more column called student_grade (of type character) to the table -

ALTER TABLE student_details ADD student_grade VARCHAR(5);

Now, the table should be like the following -

Notice that for the entries that you entered the values are null for the student_grade column. This is implicitly done by most of the RDBMS. However, you always fire an update query to update the values. Let's now see how to modify a column using Alter Table. You will change the maximum allowable size of the column student_year from 100 to 10 because allocating too much space for just three characters (student_year can either be 1st or 2nd or 3rd) is not very practical.

ALTER TABLE student_details ALTER COLUMN student_year TYPE VARCHAR(10);

You can see the result in the following figure -

You can also rename a column. Let's see how -

ALTER TABLE student_details RENAME student_id TO student_roll;

The above query renames the student_id column to student_roll. You can also verify this using a select query, and your result should look similar to the following figure -

Let's now see how you can drop a column from a table -

ALTER TABLE student_details DROP COLUMN student_year;

The query drops the column student_year and the table executing the query should be like -

Note that if the column that you are dropping is referenced by some other table, then you might get referential integrity violation errors.

You will now see how to add and drop constraints to a table.

Add and drop a constraint.

The table student_details does not have a primary key. The primary key is a combination of not null and unique constraints. Let's specify the column student_roll (it was updated from student_id) as the primary key of student_details -

ALTER TABLE student_details ADD PRIMARY KEY (student_roll);

You can add other constraints also, for example, you can add a check constraint to the student_grade column so that its values are always either A, B or C.

ALTER TABLE student_details ADD CONSTRAINT year_check CHECK (student_grade IN ('A', 'B', 'C'));

year_check is a placeholder to the constraint that you are adding to the table. Now if you attempt to insert the following entry to the table, you should get an error -

INSERT INTO student_details(student_full_name, student_grade) VALUES('David Lune','3rd');

The error looks like the following -

Dropping a constraint is also similar. You need to specify the name of the constraint which you want to drop. Let's drop the constraint which you named as year_check -

ALTER TABLE student_details DROP CONSTRAINT year_check;

Let's verify this. If the constraint year_check is actually dropped, then this query INSERT INTO student_details(student_full_name, student_grade) VALUES('David Lune','3rd'); should not cause any error. The table now contains the record -

You can play with other check constraints as well.

That is all for this tutorial!

This tutorial introduced you to the Alter Table statement to effectively modify the schema of a table. If you want to level-up your SQL skills you might be interested in taking the following courses offered by DataCamp -