CODEWITHSUNDEEP
mysqlsqldatabaseprimary-key
Cratylus
Cratylus

Reputation: 54084

Can a primary key default be NULL? Why is it described as such?

I have a table that when I describe it is: 

mysql> DESC my_table;  
+------------+-------------+------+-----+---------+----------------+  
| Field      | Type        | Null | Key | Default | Extra          |  
+------------+-------------+------+-----+---------+----------------+  
| contact_id | int(11)     | NO   | PRI | NULL    | auto_increment |  
| location   | varchar(20) | YES  |     | NULL    |                |  
| city       | varchar(20) | YES  |     | NULL    |                |  
| state      | varchar(2)  | YES  |     | NULL    |                |  
+------------+-------------+------+-----+---------+----------------+  
4 rows in set (0.01 sec)

My question is: why for the primary key contact_id the Default is displayed as NULL?
I created the table with NOT NULL for the column and the Primary Key can not be NULL anyway.
How can Null be NO and Default be NULL?

Upvotes: 5

Views: 6833

Answers (4)

Ayush
Ayush

Reputation: 1010

In your case, NULL in the default column indicates that the user didn't specify any value as the default value.

Upvotes: 0

Franz
Franz

Reputation: 53

Rest assured that even if the default column show a (NULL) value for the primary key field, it won't matter. Lets list every cases when Default has this (NULL) value :

  • if your SQL request provides a value to the primary field : Default is not called

  • if your SQL request doesnt provide a value but is an autoincrement field : Defaut is not called

  • if your SQL request doesnt provide a value and the field is not an autoincrement and can be null : Default is called and NULL is inserted

  • if your SQL request doesnt provide a value and the field is not an autoincrement and can NOT be null : Default is called but you get an error telling you that this field "cannot be null"

Hope this helps.

Upvotes: 0

user207421
user207421

Reputation: 310998

The fact that it can't be null makes the content of the 'default' column irrelevant. They are using 'null' in the 'default' column because otherwise they would need another magic value to indicate 'irrelevant', 'unused', end.

Don't worry about it.

Upvotes: 5

Deepu
Deepu

Reputation: 7610

NOT NULL in MySQL is used to indicate that the field can not be empty. In your case the Primary Key field contact_id is correctly shown as No in the attribute Null. Default clause in a data type specification indicates a default value for a column. Here your Primary Key Field contact_id does not have any default value. So it is shown as NULL.

Upvotes: 4

Related Questions