Javarevisited: How to Add, Modify and Drop Column with Default Value, NOT NULL Constraint

How to add a column in the existing table with a default value is another popular SQL interview question asked for Junior level programming job interviews. Though syntax of SQL query to add a column with default value varies a little bit from database to database, it has always been performed using ALTER keyword of ANSI SQL. Adding columns in the existing table in the MySQL database is rather easy and straight forward and we will see an example of SQL query for MySQL database which adds a column with a default value. You can also provide constraints like NULL or NOT NULL while adding a new column in the table.

In this SQL tutorial, we are adding the third column in a table called Contacts which contains name and phone of contacts. Now we want to add another column email with the default value "abc@yahoo.com".

We will use ALTER command in SQL to do that. By the way this is next in our SQL tutorials like How to join three tables in SQL and SQL query to find duplicate records in table. If you haven’t read them yet, then you may find them useful.

Add, Modify and Drop Column in MySQL table with ALTER keyword

How to add, modify and drop column in MySQL table with ALTER command

In this SQL query example we will see :

1) How to add another column in existing table with default value in MySQL database.

2) How to add column in a MySQL table with NOT NULL constraints

SQL query to drop column in MySQL table

You can also remove a column in the existing table by using alter table drop column SQL query as shown in the below example:

mysql> ALTER TABLE Contacts DROP COLUMN email;
Query OK, 4 rows affected (0.27 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM Contacts;
+-------+----------+
| name | phone |
+-------+----------+
| James | 80983243 |
| Johny | 67543212 |
| Harry | 12341234 |
| Ron | 44446666 |
+-------+----------+
4 rows IN SET (0.00 sec)

SQL query to add NOT NULL constraints to a column in MySQL table

Now we will see SQL query to add another column in an existing table with NOT NULL constraints. When you add column with NOT NULL constraints and without default value then there value will be empty.

mysql> ALTER TABLE contacts ADD COLUMN email varchar(20) NOT NULL;
Query OK, 18 rows affected (0.22 sec)
Records: 18 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM Contacts;
+-------+----------+-------+
| name | phone | email |
+-------+----------+-------+
| James | 80983243 | |
| Johny | 67543212 | |
| Harry | 12341234 | |
| Ron | 44446666 | |
+-------+----------+-------+
4 rows IN SET (0.00 sec)

mysql> INSERT INTO Contacts VALUES ("Ruby", 12345678, NULL);
ERROR 1048 (23000): COLUMN 'email' cannot be NULL

Now you can see that the email column is not accepting null values because It's created with NOT NULL constraints.

That’s all on How to add, modify and drop columns in a table in SQL. We have seen MySQL database examples but examples are generic and should work on other databases as well e.g. Oracle, SQL Server and Sybase. Effectively using NULL and NOT NULL constraints can significantly improve the code quality of both database and Server. By carefully applying constraints like NULL and NOT NULL you can effectively validate each inserted record in the table.

Related Database and SQL tutorials from Javarevisited Blog

How to find second highest salary of Employee in SQL

How to copy or backup tables using SQL query in MySQL

What is database transaction and ACID properties

Difference between truncate and delete in SQL

10 ways to use SELECT clause in SQL – MySQL Examples

Difference between correlated and noncorrelated subquery in SQL