Rails migration won't create NOT NULL constraints

Question

I have this db migration for my Rails 3.2 plugin:

class CreateIssueChangeApprovements < ActiveRecord::Migration
  def change
    create_table :issue_change_approvements do |t|
      t.timestamps
      t.references :issue, :null => false
      t.references :user, :null => false
      t.string :field_type, :limit => 30
      t.string :old_value
      t.string :value
      t.integer :approved_by_id
      t.boolean :is_closed, :default => 0
      t.string :status, :default => '', :limit => 30
    end
    add_index :issue_change_approvements, :issue_id
    add_index :issue_change_approvements, :user_id
  end
end

Once I run it against a MySQL database, I see no NOT NULL constraints on issue_id and user_id fields as well as no foreign keys.

Can anyone tell me what is the right way to do this?

UPD:

The weird thing here is that when I go to MySQL Workbench and generate a script for my newly created table, i get this:

CREATE TABLE `issue_change_approvements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `issue_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `field_type` varchar(30) DEFAULT NULL,
  `old_value` varchar(255) DEFAULT NULL,
  `value` varchar(255) DEFAULT NULL,
  `approved_by_id` int(11) DEFAULT NULL,
  `is_closed` tinyint(1) DEFAULT '0',
  `status` varchar(30) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `index_issue_change_approvements_on_issue_id` (`issue_id`),
  KEY `index_issue_change_approvements_on_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

However when I run a statement like this:

insert into issue_change_approvements(value) values('hello')

it executes successfully and adds a row with lots of NULL values.

UPD 2: This is what describe issue_change_approvements shows:

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| created_at     | datetime     | NO   |     | NULL    |                |
| updated_at     | datetime     | NO   |     | NULL    |                |
| issue_id       | int(11)      | NO   | MUL | NULL    |                |
| user_id        | int(11)      | NO   | MUL | NULL    |                |
| field_type     | varchar(30)  | YES  |     | NULL    |                |
| old_value      | varchar(255) | YES  |     | NULL    |                |
| value          | varchar(255) | YES  |     | NULL    |                |
| approved_by_id | int(11)      | YES  |     | NULL    |                |
| is_closed      | tinyint(1)   | YES  |     | 0       |                |
| status         | varchar(30)  | YES  |     |         |                |
+----------------+--------------+------+-----+---------+----------------+

Please include the table you are changing, preferably after the above migration. — user3373470
– user3373470, Commented Jul 2, 2015 at 21:16
@onebree, there is no table. It is a create_table migration — svz
– svz, Commented Jul 2, 2015 at 21:18
What do you get when you try describe issue_change_approvements; in your mysql console? — A Fader Darkly
– A Fader Darkly, Commented Jul 2, 2015 at 22:06
I guess you have not sql_mode STRICT_TRANS_TABLES enabled, otherwise you would have recieved an error on your insert statement. check sql_mode var by running show variables like "sql%". mysql 5.6.4 deafult is set sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'; — dre-hh
– dre-hh, Commented Jul 5, 2015 at 19:12

dre-hh · Accepted Answer · 2015-07-02 21:39:27Z

2

Rails does not create any constraints by default. The null: false option will just not allow null values for this sql field, but it will not add a constraint. Instead of foreign_key constraints in rails you usually just set the desired option on model relationship, e.g has_many :issues, dependent: :destroy I guess this strategy was chosen for faster schema migrations with less downtime.

However there is an option to use constraints on db level by explicitly specifying them. You can use the foreigner gem prior to rails 4.2 and with rails 4.2 the dsl for them is included by default.

See Have Some (Referential) Integrity with Foreign Keys

2.5 Foreign Key Support

answered Jul 2, 2015 at 21:39

dre-hh

8,0842 gold badges36 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

svz Over a year ago

So all these checks exist on app level only and I need to add them explicitly in order to propagate them down to DB level, right?

dre-hh Over a year ago

Yes, more or less. They do not exist by default on app level either. You have to setup this behavior on app level, by proper usage of associations. guides.rubyonrails.org/association_basics.html. Like setting dependent: :destroy.

svz Over a year ago

Yes, that's what I meant. Thanks for your answer!

A Fader Darkly Over a year ago

My experience is that setting null: false will add a database level constraint. This is different to an app level constraint, which must be added to the model as a validation.

dre-hh Over a year ago

null: false, just creates a NOT NULL column e.g ALTER TABLE table_name ADD COLUMN user_id NOT NULL As i came to rails i couldn't believe it, too, but actually this strategy of having constraints on app level rather than db level works pretty ok for the most rails apps. This is also the reason, why the feature to propagate them to db level was introduced that lately.

|

Collectives™ on Stack Overflow

Rails migration won't create NOT NULL constraints

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related