desc table in mysql say Null is No but default is NULL?

Question

I am using mysql for a project. When I did 'desc table_name', I see that the tabular output shows the Null of the field is set to No, but then 'Default' is set to NULL. How can a field not have a Null but then default is Null itself?

mysql> desc users;                                                                                 
+-----------------+------------------+------+-----+----------------------------+----------------+  
| Field           | Type             | Null | Key | Default                    | Extra          |  
+-----------------+------------------+------+-----+----------------------------+----------------+  
| id              | int(10) unsigned | NO   | PRI | NULL                       | auto_increment |  
| created_at      | timestamp        | YES  |     | NULL                       |                |  
| updated_at      | timestamp        | YES  |     | NULL                       |                |  
| email           | varchar(255)     | NO   |     | NULL                       |                |  
| password        | varchar(255)     | NO   |     | NULL                       |                |  
| remember_token  | varchar(100)     | YES  |     | NULL                       |                |  
| profile_pic     | varchar(255)     | YES  |     | img/profile/profilepic.jpg |                |  
| activation_code | varchar(255)     | YES  | UNI | NULL                       |                |  
| status_id       | int(10) unsigned | NO   | MUL | 1                          |                |  
| profile_id      | int(10) unsigned | YES  | MUL | NULL                       |                |  
+-----------------+------------------+------+-----+----------------------------+----------------+  
10 rows in set (0.00 sec)

default is null because it is the value state before the value is inserted by you . — Ankur Jyoti Phukan, Mar 14 '17 at 20:02
the No value in null column mean that for the related field the null value are not allowed . ... this value is assigned during the table/fileds creation .. — ScaisEdge, Mar 14 '17 at 20:02
if `DEFAULT is NULL` and you dont provide the field, the `INSERT` will fail because the field doesnt accept `NULL` — Juan Carlos Oropeza, Mar 14 '17 at 20:05
Can we interpret it like this. Default column says what goes in the cell, and Null (No) column is the constraint on what goes in the cell? — J. Sethi, Mar 15 '17 at 04:23

score 4 · Accepted Answer · answered Jan 13 '19 at 08:48

4

This is a perfectly legal combination. It means that when you try to insert a new row and do not explicitly specify this column's value, it would default to null and thus error out. This combination is often used as a way to force you to explicitly assign a value to the column instead of just relying on defaults.

answered Jan 13 '19 at 08:48

Mureinik

297,002
52
306
350

Since the column is a primary key it makes perfect sense that a value is always inserted. – Dougie Jan 13 '19 at 10:21

score 1 · Answer 2 · edited Feb 20 '23 at 08:53

we should know this:

Null(NO): means this column couldn't accept a "NULL" value
Null(YES): means this column could accept a "NULL" value
Default: means this column's default value :)

we should know: in MySQL, blank is not equal to NULL, if we insert nothing into a column, it will stay nothing(it will not insert a "NULL" into this column)
so, when Null(NO) comes with Default(NULL), if we insert nothing to this value, it'll have a default value "NULL" and it 'll conflict with the constraint, then it'll throw a exception

desc table in mysql say Null is No but default is NULL?

2 Answers2

Linked