mySQL - How can a column say YES for NULL and also have another DEFAULT?

Question

I Altered a column (called pub_lang) in a table to have a default value as below. However, when I describe that table, mySQL says YES for Null, and also has my inputed default value (image attached). How is that possible? Shouldn't it automatically change Null to NO, since I gave it another default value?

ALTER TABLE newbook_master MODIFY COLUMN pub_lang VARCHAR(15) DEFAULT ('English');

enter image description here

Answer 1

There's a difference between a nullable column with a default versus a non-nullable column with a default.

The difference is that you may want to explicitly set the nullable column to NULL, and not rely on its default.

mysql> create table newbook_master (pub_lang varchar(15) default 'English', pub_lang2 varchar(15) not null default 'English');
Query OK, 0 rows affected (0.01 sec)

mysql> insert into newbook_master () values ();
Query OK, 1 row affected (0.01 sec)

mysql> select * from newbook_master;
+----------+-----------+
| pub_lang | pub_lang2 |
+----------+-----------+
| English  | English   |
+----------+-----------+
1 row in set (0.00 sec)

mysql> insert into newbook_master set pub_lang=NULL, pub_lang2=NULL;
ERROR 1048 (23000): Column 'pub_lang2' cannot be null

mysql> insert into newbook_master set pub_lang=NULL, pub_lang2='French';
Query OK, 1 row affected (0.01 sec)

mysql> select * from newbook_master;
+----------+-----------+
| pub_lang | pub_lang2 |
+----------+-----------+
| English  | English   |
| NULL     | French    |
+----------+-----------+
2 rows in set (0.00 sec)