index.md (4385B)
1 --- 2 title: "Required Fields Should Be Marked NOT NULL" 3 date: 2014-09-25T00:00:00+00:00 4 draft: false 5 canonical_url: https://www.viget.com/articles/required-fields-should-be-marked-not-null/ 6 --- 7 8 *Despite some exciting advances in the field, like 9 [Node](http://nodejs.org/), [Redis](http://redis.io/), and 10 [Go](https://golang.org/), a well-structured relational database fronted 11 by a Rails or Sinatra (or Django, etc.) app is still one of the most 12 effective toolsets for building things for the web. In the coming weeks, 13 I'll be publishing a series of posts about how to be sure that you're 14 taking advantage of all your RDBMS has to offer.* 15 16 A "NOT NULL constraint" enforces that a database column does not accept 17 null values. Null, according to 18 [Wikipedia](https://en.wikipedia.org/wiki/Null_(SQL)), is 19 20 > a special marker used in Structured Query Language (SQL) to indicate 21 > that a data value does not exist in the database. Introduced by the 22 > creator of the relational database model, E. F. Codd, SQL Null serves 23 > to fulfill the requirement that all true relational database 24 > management systems (RDBMS) support a representation of "missing 25 > information and inapplicable information." 26 27 One could make the argument that null constraints in the database are 28 unnecessary, since Rails includes the `presence` validation. What's 29 more, the `presence` validation handles blank (e.g. empty string) values 30 that null constraints do not. For several reasons that I will lay out 31 through the rest of this section, I contend that null constraints and 32 presence validations should not be mutually exclusive, and in fact, **if 33 an attribute's presence is required at the model level, its 34 corresponding database column should always require a non-null value.** 35 36 ## Why use non-null columns for required fields? 37 38 ### Data Confidence 39 40 The primary reason for using NOT NULL constraints is to have confidence 41 that your data has no missing values. Simply using a `presence` 42 validation offers no such confidence. For example, 43 [`update_attribute`](http://apidock.com/rails/ActiveRecord/Persistence/update_attribute) 44 ignores validations, as does `save` if you call it with the 45 [`validate: false`](http://apidock.com/rails/v4.0.2/ActiveRecord/Persistence/save) 46 option. Additionally, database migrations that manipulate the schema 47 with raw SQL using `execute` bypass validations. 48 49 ### Undefined method 'foo' for nil:NilClass 50 51 One of my biggest developer pet peeves is seeing a 52 `undefined method 'foo' for nil:NilClass` come through in our error 53 tracking service du jour. Someone assumed that a model's association 54 would always be present, and one way or another, that assumption turned 55 out to be false. The merits of the [Law of 56 Demeter](https://en.wikipedia.org/wiki/Law_of_Demeter) are beyond the 57 scope of this post, but suffice it to say that if you're going to say 58 something like `@athlete.team.name` in your code, you better be damn 59 sure that a) the athlete's `team_id` has a value and b) it corresponds 60 to the ID of an actual team. We'll get to that second bit in our 61 discussion of foreign key constraints in a later post, but the first 62 part, ensuring that `team_id` has a value, demands a `NOT NULL` column. 63 64 ### Migration Issues 65 66 Another benefit of using `NOT NULL` constraints is that they force you 67 to deal with data migration issues. Suppose a change request comes in to 68 add a required `age` attribute to the `Employee` model. The easy 69 approach would be to add the column, allow it to be null, and add a 70 `presence` validation to the model. This works fine for new employees, 71 but all of your existing employees are now in an invalid state. If, for 72 example, an employee then attempts a password reset, updating their 73 `password_reset_token` field would fail due to the missing age value. 74 75 If you'd created the `age` column to require a non-null value, you would 76 have been forced to deal with the issue of existing users immediately 77 and thus avoided this issue. That said, there's no obvious value for 78 what to fill in for all of the existing users' ages, but better to have 79 that discussion at development time than to spend weeks or months 80 dealing with the fallout of invalid users in the system. 81 82 *** 83 84 I hope I've laid out a case for using non-null constraints for all 85 required database fields for great justice. In the next post, I'll show 86 the proper way to add non-null columns to existing tables.