MySQL, AUTO_INCREMENT, and NOT NULL

Question

In a MySQL table, you can obviously set an INT as both NOT NULL and AUTO_INCREMENT, but while they both work together, is the former really necessary when you have the latter?

That aside, is there any performance, memory, storage, or speed bonus for dropping NOT NULL?

Answer 1

As a matter of good design, if a column requires a value in order to properly function, it should be declared NOT NULL.

That said, as a practical matter, declaring an autoincrement column as nullable means that you can pass a null value to it explicitly and still get a value generated for the column.

http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html

In terms of improving efficiency and space, it's better to make the column UNSIGNED, since it will never take a negative value, and to use the smallest data type that will keep pace with the table's size.

By definition, all column constraints impose some penalty on performance, since the server must devote resources to storing and checking the constraints.

But this is minuscule, especially for something routine like checking for null values, and again, it's worth the expense if it enforces validity in your records.