Wednesday, December 26, 2012

How to add, modify and drop column with default value, NOT NULL constraint – MySQL database Example

How to add column in existing table with default value is another popular SQL interview question asked for Junior level programming job interviews. Though syntax of SQL query to add column with default value varies little bit from database to database, it always been performed using ALTER keyword of ANSI SQL. Adding column in existing table in MySQL database is rather easy and straight forward and we will see example of SQL query for MySQL database which adds a column with default value. You can also provide constraints like NULL or NOT NULL while adding new column in table. In this SQL tutorial  we are adding third column in a table called Contacts which contains name and phone of contacts. Now we want to add another column email with 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 e.g. 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 commandIn 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

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

mysql> ALTER TABLE contacts ADD COLUMN email varchar(20) DEFAULT "abc@yahoo.com"
    -> ;
Query OK, 4 rows affected (0.20 sec)
Records: 4  Duplicates: 0  Warnings: 0

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


SQL query to drop column in MySQL table
You can also remove column in existing table by using alter table drop column SQL query as shown in 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 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 email column is not accepting null values because its created with NOT NULL constraints.

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


Related Database and SQL tutorials from Javarevisited Blog

No comments :

Post a Comment