Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

When should I be using "NOT NULL" in a MySQL table and are there any benefits?

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.

like image 818
Chris Gaunt Avatar asked Oct 27 '08 16:10

Chris Gaunt


People also ask

Why do we use not null in mysql?

The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

When should I use NOT NULL in SQL?

A NOT NULL constraint in SQL is used to prevent inserting NULL values into the specified column, considering it as a not accepted value for that column. This means that you should provide a valid SQL NOT NULL value to that column in the INSERT or UPDATE statements, as the column will always contain data.

Should you use not null?

You must therefore use NOT NULL for all columns that cannot legitimately contain nulls. If you specify that a column is NOT NULL , you are defining a constraint that ensures that that the column can never hold or accept NULL , so you can't accidentally leave the value out.

Why should we avoid NULL values in SQL?

They should be avoided to avoid the complexity in select & update queries and also because columns which have constraints like primary or foreign key constraints cannot contain a NULL value.


1 Answers

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.

like image 90
Daniel Spiewak Avatar answered Nov 08 '22 23:11

Daniel Spiewak