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 "firstname.lastname@example.org". 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
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 column in existing table by using alter table drop column SQL query as shown in below example:
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.
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