10

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.

L457
  • 1,002
  • 1
  • 13
  • 33
  • When using references, (there is no need to specify `index: true`)[http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_reference]. ActiveRecord (doesn't heavily rely on DB constraints)[http://guides.rubyonrails.org/active_record_migrations.html#active-record-and-referential-integrity], but it would be kind of crazy to have a reference field and not having the field indexed, no? – sekmo Mar 04 '18 at 10:31

4 Answers4

9

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
Johnny Dương
  • 753
  • 3
  • 8
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.

L457
  • 1,002
  • 1
  • 13
  • 33
  • sqlite is correct, this is how it's supposed to work. There are other relations databases which behave this way. – dimakura Aug 31 '15 at 11:18
  • 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. – jing May 30 '16 at 17:34
1

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

sekmo
  • 1,517
  • 18
  • 27
-1

The message basically says that you cannot create a "not null" column without defining default value for it.

I think you should remove null: false option.

To prevent NULL values appear in POSTS table, add

validates :category, presence: true

to your Post model.

See Also Also check this post https://robots.thoughtbot.com/referential-integrity-with-foreign-keys to get better understanding of foreign keys in Rails.

dimakura
  • 7,575
  • 17
  • 36
  • 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 – L457 Aug 31 '15 at 07:37
  • @Lin457 I've added a link to a blog post where you can read more about securing database part of the foreign key. – dimakura Aug 31 '15 at 07:43
  • @Lin457 it's not a bug with sqlite3. I'm not sure about MySQL, but several other databases also have the same behavior. – dimakura Aug 31 '15 at 08:59
  • 7
    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) – rpearce Feb 22 '16 at 18:04