- Trending Categories
- 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
How do I modify a MySQL column to allow NULL?
For our example, let us create a table with NOT NULL constraint. After that, we will modify a column to allow NULL.
The following is the query to create a table with NOT NULL constraint.
mysql> create table AllowNullDemo -> ( -> id int not null -> ); Query OK, 0 rows affected (0.48 sec)=
Insert records with the help of INSERT command. The query is as follows.
mysql> insert into AllowNullDemo values(); Query OK, 1 row affected, 1 warning (0.19 sec) mysql> insert into AllowNullDemo values(); Query OK, 1 row affected, 1 warning (0.15 sec)
The query to display records.
mysql> select *from AllowNullDemo;
Here is the output. The value 0 is displayed, since we haven’t added any value while using INSERT command above.
+----+ | id | +----+ | 0 | | 0 | +----+ 2 rows in set (0.00 sec)
Here is the syntax to allow NULL value.
alter table yourTableName modify column yourColumnName datatype;
Apply the above syntax to modify the column to allow NULL. The query is as follows.
mysql> alter table AllowNullDemo modify column id int; Query OK, 0 rows affected (1.59 sec) Records: 0 Duplicates: 0 Warnings: 0
After executing the above query, you can insert NULL value to that column because the column is modified successfully above.
mysql> insert into AllowNullDemo values(); Query OK, 1 row affected (0.15 sec)
Display records to check whether the last value inserted is NULL or not.
mysql> select *from AllowNullDemo;
The following is the output wherein NULL value is visible now.
+------+ | id | +------+ | 0 | | 0 | | NULL | +------+ 3 rows in set (0.00 sec)
Using the above method, we can easily modify a MySQL column to allow NULL.