Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Create a column on my table that allows null but is set by default to empty (not null)?
You need to use default keyword for this. The syntax is as follows −
alter table yourTableName add yourColumnName yourDataType NULL Default '';
To understand the above syntax, let us create a table. The query to create a table is as follows −
mysql> create table AllowNullDefaulNotNullDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserName varchar(20), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.65 sec)
Let us add a new column that allow NULL value but default value set to NOT NULL. The query is as follows −
mysql> alter table AllowNullDefaulNotNullDemo add UserAddress varchar(20) NULL Default''; Query OK, 0 rows affected (1.04 sec) Records: 0 Duplicates: 0 Warnings: 0
Now check the description of table using DESC command. The query is as follows −
mysql> desc AllowNullDefaulNotNullDemo;
The following is the output −
+-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | Id | int(11) | NO | PRI | NULL | auto_increment | | UserName | varchar(20) | YES | | NULL | | | UserAddress | varchar(20) | YES | | | | +-------------+-------------+------+-----+---------+----------------+ 3 rows in set (0.17 sec)
Now you can insert some records in the table using insert command. In the below query the column UserAddress allow NULL value and default set value to NOT NULL.
Let us insert records in this column. The query is as follows −
mysql> insert into AllowNullDefaulNotNullDemo(UserAddress) values('US');
Query OK, 1 row affected (0.14 sec)
mysql> insert into AllowNullDefaulNotNullDemo(UserAddress) values(NULL);
Query OK, 1 row affected (0.16 sec)
mysql> insert into AllowNullDefaulNotNullDemo() values();
Query OK, 1 row affected (0.12 sec)Check specific records of the newly added column i.e. UserAddress. The query is as follows −
mysql> select UserAddress from AllowNullDefaulNotNullDemo;
The following is the output −
+-------------+ | UserAddress | +-------------+ | US | | NULL | | | +-------------+ 3 rows in set (0.00 sec)
Updated on: 30-Jul-2019
520 Views
Advertisements