[RESOLVED] NOT NULL in AUTO_INCREMENT PRIMARY KEY ?

halojoy

<?php

$sql = "CREATE TABLE bands (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(32) UNIQUE,
born VARCHAR(32),
died VARCHAR(32),
band VARCHAR(32),
lineup VARCHAR(64)
)";
$db->Exec($sql);

$sql = "INSERT INTO bands VALUES (
NULL,
'John Lennon',
'1940 Liverpool',
'1980 New York',
'The Beatles',
'vocals, guitar, harmonica, piano, keyboards')";
$db->Exec($sql);

$sql = "INSERT INTO bands VALUES (
NULL,
'Paul McCartney',
'1942 Liverpool',
'',
'The Beatles',
'vocals, bass, lead guitar, piano, keyboards')";
$db->Exec($sql);
?>

I use the above script.
It works perfect to insert. I use NULL as a 'dummy' for the 'id' in insert.

As you can see I use

id INTEGER AUTO_INCREMENT PRIMARY KEY,

Now in many places including the MySQL Reference Manual
I see they use NOT NULL

id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,

If I do like I do, not using NOT NULL in my AUTO_INCREMENT PRIMARY.
what is the problem??
As I said, it works perfectly without NOT NULL.
Even when INSERT NULL value!

bradgrafelman

If you don't specify "NULL" or "NOT NULL", MySQL assumes you wanted "NULL".

In the case of an AUTO_INCREMENT column, however, a value of "NULL" has a special meaning:

An integer or floating-point column can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value.

Why do some specify "NOT NULL"? They're probably just writing more explicit SQL to avoid the ambiguity.

halojoy

Thanks bradgrafelman.

More explicit standard SQL, yes.
I may include it, as this is what MySQL recommends. It will not hurt.

I will start using

id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,

bradgrafelman

Just tried something out on my local MySQL server:

mysql> CREATE TABLE testme (
    -> id INTEGER NULL AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> SHOW CREATE TABLE testme;
+--------+---------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                              |
+--------+---------------------------------------------------------------------------------------------------------------------------+
| testme | CREATE TABLE `testme` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

In other words, the default may be "NULL", but even if you try to force an AUTO_INCREMENT column to be "NULL," MySQL forces the column to be "NOT NULL" - likely because "NULL" values have special meaning.

halojoy

I also found this
in http://dev.mysql.com/doc/refman/5.0/en/create-table.html

A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL.
If they are not explicitly declared as NOT NULL,
MySQL declares them so implicitly (and silently).

So regardless if I declare an AUTO_INCREMENT PRIMARY KEY as NOT NULL or I dont
it is in fact a NOT NULL column