By default, most MySQL columns are set to have null default value. But sometimes one or more columns in your MySQL table may not be nullable. In such cases, if you do not provide default values for these columns then when you insert values into your table, MySQL will throw an error. In such cases, you may need to modify MySQL column to allow null as default value. In this article, we will learn how to do this.
How to Modify MySQL Column to Allow Null
Let us say you have the following table.
mysql> create table sales(id int default null, amount int default null, order_date date);
Let us look at how the columns were created.
mysql> describe sales; +------------+---------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+------------+-------+ | id | int(11) | YES | | NULL | | | amount | int(11) | YES | | NULL | | | order_date | date | NO | | 0000-00-00 | | +------------+---------+------+-----+------------+-------+
As you can see in the above result, the order_date column does not have null default value. If you try inserting row in this table without mentioning value for order_date column, MySQL will give an error.
mysql> insert into sales(id, amount) values(1, 300); ERROR 1364 (HY000): Field 'order_date' doesn't have a default value
To tackle this problem, you need to make order_date column nullable, that is, set its default value as null. You can do this using modify column statement, as shown below. The key thing is to mention null or default null keywords after you mention the data type of your column.
mysql> alter table sales modify column order_date date null; OR mysql> alter table sales modify column order_date date default null; mysql> describe sales; +------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | amount | int(11) | YES | | NULL | | | order_date | date | YES | | NULL | | +------------+---------+------+-----+---------+-------+
Now if you try inserting a row with null value for order_date it should work.
mysql> insert into sales(id, amount) values(1, 30); mysql> select * from sales; +------+--------+------------+ | id | amount | order_date | +------+--------+------------+ | 1 | 30 | NULL | +------+--------+------------+
In this article, we have learnt how to modify MySQL column to allow nulls.
Also read:
How to Schedule Multiple Cron Jobs in One Crontab
How to POST JSON Data in Python
How to Remove SSL Certificate & SSH Passphrase in Linux
How to Capture Linux Signal in Python
How to Send Signal from Python