Rails4 migration- adding null: false to an add_reference method?

Question

I've just started learning rails so sorry if the answer to this if fairly obvious.

I've added migrations for posts and categories tables in my app and am now adding a reference to categories in my posts table with a default value of not null using the following line:

add_reference :posts, :category, index: true, foreign_key: true, null: false

however I get the following error on running the migration:

SQLite3::SQLException: Cannot add a NOT NULL column with default value NULL: ALTER TABLE "posts" ADD "category_id" integer NOT NULL

I've tried reading through the api, but couldn't figure out what I am doing wrong.

Solution 1

After a bit of further reading, this seems to be a bug with sqlite3. Apparently you cannot add a not null value to a reference like above while making changes to an existing table(this would work in MYSQL). However you can do this when creating a table from scratch. Managed to get this to work by adding the above line to my create_table migration for categories instead.

Solution 2

maybe sqlite3 don't allow to do that, try

add_reference :posts, :category, index: true, foreign_key: true, null: false
change_column :posts, :category_id, :integer, null: false

Solution 3

Note: when using references, there is no need to specify index: true.

Comments

[L457]

Thanks for replying, validation works normally on my models. I read on a tutorial that its best practice to add validation at the database level as well as requests can occasionally slip through model validations, specially with heavy traffic, which is why i've been trying the above. Is there a better way to add a not null value to this column, or is it fine just leaving validation to the model in this case? thanks again

[dimakura]

@Lin457 I've added a link to a blog post where you can read more about securing database part of the foreign key.

[dimakura]

@Lin457 it's not a bug with sqlite3. I'm not sure about MySQL, but several other databases also have the same behavior.

[dimakura]

sqlite is correct, this is how it's supposed to work. There are other relations databases which behave this way.

[rpearce]

I think it's a generally good idea to enforce things at the database level when possible that are vital to a table (and the application)

[jing]

I don't understand why sqlite is correct in this case. A "must have value" foreign key is a normal requirement supported accross wide scale of database engines. However I didn't find good way to add reference during create_table as L457 suggested.