CODEWITHSUNDEEP
mysql
user1653028
user1653028

Reputation: 11

Can I configure MySQL so that newly added columns allow nulls by default?

Our developers frequently forget to specify that nulls are allowed when adding columns to existing tables. MySQL defaults to not allowing nulls if Null is not included when using the Alter Table command to add a column

Is there a way to configure MySQL so that new columns allow nulls unless Not Null is explicitly included with the alter table command?

We use PHPMyAdmin to alter tables so a method of making all newly added fields allow nulls through PHPMyAdmin would work.

Upvotes: 1

Views: 83

Answers (2)

Mike Brant
Mike Brant

Reputation: 71424

You can specify the DEFAULT NULL when creating the new column like:

ALTER TABLE table
ADD COLUMN column VARCHAR(255) DEFAULT NULL;

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181380

MySQL's default behavior is allowing nulls by default on alter table commands. It probably has something to do with PHPMyAdmin.

mysql> desc foo;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| id        | int(11) | YES  |     | NULL    |       | 
| client_id | int(11) | YES  |     | NULL    |       | 
| item_id   | int(11) | YES  |     | NULL    |       | 
+-----------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table foo add (a char(1));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc foo;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| id        | int(11) | YES  |     | NULL    |       | 
| client_id | int(11) | YES  |     | NULL    |       | 
| item_id   | int(11) | YES  |     | NULL    |       | 
| a         | char(1) | YES  |     | NULL    |       | 
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Upvotes: 2

Related Questions