index.md (7160B)
1 --- 2 title: "Adding a NOT NULL Column to an Existing Table" 3 date: 2014-09-30T00:00:00+00:00 4 draft: false 5 canonical_url: https://www.viget.com/articles/adding-a-not-null-column-to-an-existing-table/ 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 ASSUMING MY [LAST 17 POST](/elsewhere/required-fields-should-be-marked-not-null/) 18 CONVINCED YOU of the *why* of marking required fields `NOT NULL`, the 19 next question is *how*. When creating a brand new table, it's 20 straightforward enough: 21 22 ```sql 23 CREATE TABLE employees ( 24 id integer NOT NULL, 25 name character varying(255) NOT NULL, 26 created_at timestamp without time zone, 27 ... 28 ); 29 ``` 30 31 When adding a column to an existing table, things get dicier. If there 32 are already rows in the table, what should the database do when 33 confronted with a new column that 1) cannot be null and 2) has no 34 default value? Ideally, the database would allow you to add the column 35 if there is no existing data, and throw an error if there is. As we'll 36 see, depending on your choice of database platform, this isn't always 37 the case. 38 39 ## A Naïve Approach 40 41 Let's go ahead and add a required `age` column to our employees table, 42 and let's assume I've laid my case out well enough that you're going to 43 require it to be non-null. To add our column, we create a migration like 44 so: 45 46 ```ruby 47 class AddAgeToEmployees < ActiveRecord::Migration 48 def change 49 add_column :employees, :age, :integer, null: false 50 end 51 end 52 ``` 53 54 The desired behavior on running this migration would be for it to run 55 cleanly if there are no employees in the system, and to fail if there 56 are any. Let's try it out, first in Postgres, with no employees: 57 58 ``` 59 == AddAgeToEmployees: migrating ============================================== 60 -- add_column(:employees, :age, :integer, {:null=>false}) 61 -> 0.0006s 62 == AddAgeToEmployees: migrated (0.0007s) ===================================== 63 ``` 64 65 Bingo. Now, with employees: 66 67 ``` 68 == AddAgeToEmployees: migrating ============================================== 69 -- add_column(:employees, :age, :integer, {:null=>false}) 70 rake aborted! 71 StandardError: An error has occurred, this and all later migrations canceled: 72 73 PG::NotNullViolation: ERROR: column "age" contains null values 74 ``` 75 76 Exactly as we'd expect. Now let's try SQLite, without data: 77 78 ``` 79 == AddAgeToEmployees: migrating ============================================== 80 -- add_column(:employees, :age, :integer, {:null=>false}) 81 rake aborted! 82 StandardError: An error has occurred, this and all later migrations canceled: 83 84 SQLite3::SQLException: Cannot add a NOT NULL column with default value NULL: ALTER TABLE "employees" ADD "age" integer NOT NULL 85 ``` 86 87 Regardless of whether or not there are existing rows in the table, 88 SQLite won't let you add `NOT NULL` columns without default values. 89 Super strange. More information on this ... *quirk* ... is available on 90 this [StackOverflow 91 thread](http://stackoverflow.com/questions/3170634/how-to-solve-cannot-add-a-not-null-column-with-default-value-null-in-sqlite3). 92 93 Finally, our old friend MySQL. Without data: 94 95 ``` 96 == AddAgeToEmployees: migrating ============================================== 97 -- add_column(:employees, :age, :integer, {:null=>false}) 98 -> 0.0217s 99 == AddAgeToEmployees: migrated (0.0217s) ===================================== 100 ``` 101 102 Looks good. Now, with data: 103 104 ``` 105 == AddAgeToEmployees: migrating ============================================== 106 -- add_column(:employees, :age, :integer, {:null=>false}) 107 -> 0.0190s 108 == AddAgeToEmployees: migrated (0.0191s) ===================================== 109 ``` 110 111 It ... worked? Can you guess what our existing user's age is? 112 113 ``` 114 > be rails runner "p Employee.first" 115 #<Employee id: 1, name: "David", created_at: "2014-07-09 00:41:08", updated_at: "2014-07-09 00:41:08", age: 0> 116 ``` 117 118 Zero. Turns out that MySQL has a concept of an [*implicit 119 default*](http://stackoverflow.com/questions/22868345/mysql-add-a-not-null-column/22868473#22868473), 120 which is used to populate existing rows when a default is not supplied. 121 Neat, but exactly the opposite of what we want in this instance. 122 123 ### A Better Approach 124 125 What's the solution to this problem? Should we just always use Postgres? 126 127 [Yes.](https://www.youtube.com/watch?v=bXpsFGflT7U) 128 129 But if that's not an option (say your client's support contract only 130 covers MySQL), there's still a way to write your migrations such that 131 Postgres, SQLite, and MySQL all behave in the same correct way when 132 adding `NOT NULL` columns to existing tables: add the column first, then 133 add the constraint. Your migration would become: 134 135 ```ruby 136 class AddAgeToEmployees < ActiveRecord::Migration 137 def up 138 add_column :employees, :age, :integer 139 change_column_null :employees, :age, false 140 end 141 142 def down 143 remove_column :employees, :age, :integer 144 end 145 end 146 ``` 147 148 Postgres behaves exactly the same as before. SQLite, on the other hand, 149 shows remarkable improvement. Without data: 150 151 ``` 152 == AddAgeToEmployees: migrating ============================================== 153 -- add_column(:employees, :age, :integer) 154 -> 0.0024s 155 -- change_column_null(:employees, :age, false) 156 -> 0.0032s 157 == AddAgeToEmployees: migrated (0.0057s) ===================================== 158 ``` 159 160 Success -- the new column is added with the null constraint. And with 161 data: 162 163 ``` 164 == AddAgeToEmployees: migrating ============================================== 165 -- add_column(:employees, :age, :integer) 166 -> 0.0024s 167 -- change_column_null(:employees, :age, false) 168 rake aborted! 169 StandardError: An error has occurred, this and all later migrations canceled: 170 171 SQLite3::ConstraintException: employees.age may not be NULL 172 ``` 173 174 Perfect! And how about MySQL? Without data: 175 176 ``` 177 == AddAgeToEmployees: migrating ============================================== 178 -- add_column(:employees, :age, :integer) 179 -> 0.0145s 180 -- change_column_null(:employees, :age, false) 181 -> 0.0176s 182 == AddAgeToEmployees: migrated (0.0323s) ===================================== 183 ``` 184 185 And with: 186 187 ``` 188 == AddAgeToEmployees: migrating ============================================== 189 -- add_column(:employees, :age, :integer) 190 -> 0.0142s 191 -- change_column_null(:employees, :age, false) 192 rake aborted! 193 StandardError: An error has occurred, all later migrations canceled: 194 195 Mysql2::Error: Invalid use of NULL value: ALTER TABLE `employees` CHANGE `age` `age` int(11) NOT NULL 196 ``` 197 198 BOOM. [Flawless victory.](https://www.youtube.com/watch?v=kXuCvIbY1v4) 199 200 *** 201 202 To summarize: never use `add_column` with `null: false`. Instead, add 203 the column and then use `change_column_null` to set the constraint for 204 correct behavior regardless of database platform. In a follow-up post, 205 I'll focus on what to do when you don't want to simply error out if 206 there is existing data, but rather migrate it into a good state before 207 setting `NOT NULL`.