How to Adding a Column in MySQL

In this tutorial, we aim at exploring the concept of adding a column in a table in MySQL.

Many-a-times, businesses and organizations need to add a particular column before or after a particular column in MySQL. It becomes critically important to ensure data consistency and integrity.

It also has a wide array of uses in both the public and the private or individualistic needs of analysts using MySQL.

For example, if a product-based company needs to add a column named year after the column name expiry date, they need to use a method to help them get this done.

Data analysts can use the ALTER TABLE command and apply an AFTER clause for the field under consideration to get this done.

Use the ALTER TABLE Statement to Add a Column in a Table in MySQL

Let us understand how this method works and get this done in a MySQL database.

However, before we begin, we create a dummy dataset to work with. Here we create a table, student_details_table, along with a few rows.

-- create the table student_details_table
CREATE TABLE student_details_table(
  stu_id int,
  stu_firstName varchar(255),
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id, stu_firstName)
);
-- insert rows to the table student_details_table
INSERT INTO student_details_table(stu_id,stu_firstName,stu_lastName)
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

Here as we can see, we have set the stu_id and stu_firstName as the primary key for our table student_details_table. It would ensure that the values in this column cannot be duplicated or NULL.

It should also be noted that a primary key cannot possess Null values in its column. We will certainly get an error if we push or insert Null values in this column.

Sometimes, it becomes necessary for organizations to get rid of this key to insert multiple similar values or null values.

The query above creates a table with rows of the students’ first and last names. To view the entries in the data, we use the following code.

SELECT * FROM student_details_table;

The query would give the following output.

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Now let us use the DESCRIBE statement to understand our table in greater depth. This operation can be performed using the following query.

DESCRIBE name_of_the_table;

In our case, we would need to write the following query to understand the details of the table student_details_table.

DESCRIBE student_details_table;

This statement would help us fetch the table’s intricate details like the data type associated with each column, different columns and their names, the keys associated with each column, and any extra information relevant to the table.

The query would give us the following output.

Field				Type			Null	Key		Default		Extra
stu_id				int				NO		PRI
stu_firstName		varchar(255)	NO		PRI
stu_lastName		varchar(255)	YES

Now, let us add a column status indicating the student’s attendance status with 0 as absent and 1 as present.

We use the ALTER TABLE statement to get this done. This statement can help create altercations, additions, deletions, and updates in a particular table in MySQL.

We can use the following query to add a column status after the stu_id column in MySQL.

ALTER TABLE `student_details_table`
ADD COLUMN `status` int(10) unsigned NOT NULL
AFTER `stu_id`;

As shown from the table and the query, we will add a column named status, which indicates whether a student is present or not with the values 0 and 1. It is also called a binary column as it takes only two values.

The query would add a new column and can be verified using the DESCRIBE statement again. The statement can be illustrated as follows.

DESCRIBE student_details_table;

The query would help us fetch the following output.

Field				Type			Null	Key		Default		Extra
stu_id				int				NO		PRI
status              int unsigned    NO
stu_firstName		varchar(255)	NO		PRI
stu_lastName		varchar(255)	YES

As shown in the table, we have a new column named status placed in our student_details_table, after the stu_id column. We can also visualize the datatype, NULL value possibility, and key with extra column information.

Therefore, with the help of the ALTER TABLE command and the DESCRIBE statement, we can efficiently add a column in a specific part in a table in MySQL.

Some other related topics that can help learn the concept better are below.

• ALTER TABLE statement in MySQL.
• DESCRIBE function in MySQL.