How can we remove NOT NULL constraint from column of existing MySQL table?

We can remove NOT NULL constraints from columns of existing tables using ALTER TABLE statement.

Example

Suppose we have a table "test123" which has NOT NULL constraint on column "ID" as follows -

mysql> DESCRIBE test123;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra    |
+-------+---------+------+-----+---------+-------+
| ID    | int(11) | NO   |     |   NULL  |       |
| Date  | date    | YES  |     |   NULL  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.04 sec)

Now if we want to remove NOT NULL constraint , then we can use the ALTER TABLE statement as shown below-

mysql> ALTER TABLE test123 MODIFY ID INT NULL;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESCRIBE test123;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+------ +---------+------+-----+---------+-------+
| ID    | int(11) | YES  |     |   NULL  |       |
| Date  | date    | YES  |     |   NULL  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.06 sec)

The above result set shows that the NOT NULL constraint on column "ID" has been removed.

In the above query, the keyword NULL after the keyword MODIFY is optional. The following query will also produce the same results as above -

mysql> ALTER TABLE test123 MODIFY ID INT;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0

The above is the detailed content of How can we remove NOT NULL constraint from column of existing MySQL table?. For more information, please follow other related articles on the PHP Chinese website!