MySQL – remove NOT NULL constraint from a table


[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)