Thursday, June 13, 2024
HomeJavaAdd, Modify and Drop Column with Default Worth, NOT NULL Constraint –...

Add, Modify and Drop Column with Default Worth, NOT NULL Constraint – MySQL Instance


Disclosure: This text might comprise affiliate hyperlinks. Once you buy, we might earn a small fee.

add a column within the current desk with a default worth is one other fashionable
SQL
interview query
requested for Junior degree programming
job interviews
. Although syntax of SQL question so as to add a column with default worth
varies just a little bit from database to database, it has all the time been carried out utilizing
ALTER key phrase of ANSI SQL. Including columns within the current desk within the MySQL database
is fairly simple and straight ahead and we are going to see an instance of SQL question for MySQL database which provides a column with a default worth. You may as well present
constraints like NULL or NOT NULL whereas
including a brand new column within the desk.



On this SQL tutorial, we’re including the third column in a desk known as Contacts which
accommodates
identify and telephone of
contacts. Now we need to add one other column e mail with the default worth
“abc@yahoo.com”. 


We are going to
use ALTER command in SQL to try this. By the best way that is subsequent in our SQL
tutorials like  
How
to affix three tables in SQL
and SQL
question to seek out duplicate information in desk
. In case you haven’t learn them but,
then chances are you’ll discover them helpful.



Add, Modify and Drop Column in MySQL desk with ALTER key phrase

How to add, modify and drop column in MySQL table with ALTER commandOn this SQL question instance we are going to see :
1) add one other column in current desk with default worth in MySQL database.

2) add column in a MySQL desk with NOT NULL constraints

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

mysql> ALTER
TABLE contacts ADD COLUMN
e mail varchar(20) DEFAULT
“abc@yahoo.com”
    -> ;
Question OK, 4
rows affected (0.20
sec)
Data: 4  Duplicates: 0  Warnings: 0

mysql> SELECT
* FROM
Contacts;
+——-+———-+—————+
| identify  |
telephone    | e mail    
    |
+——-+———-+—————+
| 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
question to drop column in MySQL desk

You may as well take away a column within the current desk through the use of alter desk drop
column SQL question as proven within the beneath instance:

mysql>
ALTER TABLE
Contacts DROP COLUMN e mail;
Question OK, 4
rows affected (0.27
sec)
Data: 4  Duplicates: 0  Warnings: 0

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

SQL
question so as to add NOT NULL constraints to a column in MySQL desk

Now we are going to see SQL question so as to add one other column in an current desk with NOT
NULL constraints. Once you add column with NOT NULL  constraints and with out default worth then
there worth shall be empty.

mysql>
ALTER TABLE
contacts ADD COLUMN e mail varchar(20) NOT NULL;
Question OK, 18
rows affected (0.22
sec)
Data: 18  Duplicates: 0  Warnings: 0

mysql> SELECT
* FROM
Contacts;
+——-+———-+——-+
| identify  |
telephone    | e mail |
+——-+———-+——-+
| 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 ‘e mail’
can’t be NULL

Now you’ll be able to see that the e-mail column shouldn’t be accepting null values as a result of
It is created with NOT NULL constraints.

That’s all on add, modify and drop columns in a desk in SQL.
We now have seen MySQL database examples however examples are generic and will work on different databases as properly e.g. Oracle,
SQL
Server
and Sybase.
Successfully utilizing
NULL and NOT NULL
constraints can considerably enhance the code high quality of each database and Server.
By fastidiously making use of constraints like
NULL and NOT
NULL
you’ll be able to successfully validate every inserted report within the desk.

Associated Database and SQL tutorials from Javarevisited Weblog



RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments