2

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?

eggbertx
  • 463
  • 4
  • 14
  • 1
    It's an interesting question, you could possibly refer to here: http://stackoverflow.com/questions/3503089/mysql-why-not-null-add-to-primary-key-field – Paul Lo Feb 27 '15 at 17:18
  • Ah, so it being the primary key, NOT NULL is automatically assumed? – eggbertx Feb 27 '15 at 17:35

1 Answers1

3

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.

  • I guess I should have specified this was the primary key, named `id`, and so it's obviously unsigned. And in my code, all insertions into the table ignore the id. So in conclusion, I should keep it there, just in case? – eggbertx Feb 27 '15 at 17:36
  • 1
    My best advice, and it's only that, is that if a column needs to hold valid data, it should have a NOT NULL constraint, just as a best practice. –  Feb 27 '15 at 20:45