Raja PLECI: How to add, modify and drop column with default value, NOT NULL constraint â€“ MySQL database Example

Kamis, 12 Juni 2014

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 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 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

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