[adsense id=”0514458240″ width=”468″ height=”60″]
MySQL allows table columns to be created with the NOT NULL constraint. This implies that the field needs to be populated with a value that cannot be the NULL value.
In some cases we may need to remove this constraint. This is achieved using the following command:
ALTER TABLE table MODIFY field datatype NULL;
For example lets image we have a table which contains a NOT NULL field and we wish to convert this to NULL. The table name in question is youtube and the field videourl:
mysql> desc youtube; +----------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | videourl | varchar(1024) | NO | | | | | thumburl | varchar(1024) | YES | | NULL | | | title | varchar(1024) | YES | | NULL | | | content | varchar(1024) | YES | | NULL | | +----------+---------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> ALTER TABLE youtube MODIFY videourl VARCHAR(1024) NULL; Query OK, 7 rows affected (0.03 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> desc youtube; +----------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | videourl | varchar(1024) | YES | | NULL | | | thumburl | varchar(1024) | YES | | NULL | | | title | varchar(1024) | YES | | NULL | | | content | varchar(1024) | YES | | NULL | | +----------+---------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
The reverse operation may also be performed, changing from a NULL to NOT NULL constraint. However, if the field contains a NULL value then this operation will fail with an error. To determine whether a NULL value is in the field issue the following command:
mysql> select * from youtube where videourl is null; Empty set (0.00 sec)
We are now sure that the operation will happen correctly so we now issue the ALTER TABLE command:
mysql> ALTER TABLE youtube MODIFY videourl VARCHAR(1024) NOT NULL; Query OK, 7 rows affected (0.06 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> desc youtube; +----------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | videourl | varchar(1024) | NO | | | | | thumburl | varchar(1024) | YES | | NULL | | | title | varchar(1024) | YES | | NULL | | | content | varchar(1024) | YES | | NULL | | +----------+---------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)