When should I be using "NOT NULL" in a MySQL table and are there any benefits?
migration, mysql, null, ruby-on-rails
Solution
Not much if you mean in terms of performance or storage efficiency. However, it's just good practice to push as many of your low-level constraints into the database layer. For one thing, it guarantees that a subtle bug in Rails isn't going to lead to some randomly `NULL` data in a not-null field. Likewise, if you ever run another app against the same database, it will be extremely helpful to have the constraints in a central place for maintenance and to avoid duplication.
Problem
I have the following rails migration: ``` create_table :articles do |t| t.integer :user_id, :allow_null => false t.integer :genre_id, :allow_null => false t.string :url, :limit => 255, :allow_null => false t.string :title, :limit => 60, :allow_null => false t.text :summary, :limit => 350, :allow_null => false t.integer :votes_count, :default => 0 t.datetime :published_at, :default => nil t.timestamps end ``` All the fields that are "NOT NULL" are validated in the model first, so I'm wondering if I need to bother having allow_null in the migration? I'm not sure what benefits "NOT NULL" gives to the database, if any.