Rails: PG::NotNullViolation: ERROR: null value in column "id" violates not-null constraint

Question

Here is the error when saving a record into postgres database on rails 3.2.12 & pg 9.3:

ActiveRecord::StatementInvalid (PG::NotNullViolation: ERROR:  null value in column "id" violates not-null constraint
: INSERT INTO "sw_module_infox_module_infos" ("about_controller", "about_init", "about_log", "about_misc_def", "about_model", "about_onboard_data", "about_subaction", "about_view", "about_workflow", "active", "api_spec", "category_id", "created_at", "last_updated_by_id", "module_desp", "name", "submit_date", "submitted_by_id", "updated_at", "version", "wf_state") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21) RETURNING "id"):
  activerecord (3.2.12) lib/active_record/connection_adapters/postgresql_adapter.rb:1166:in `get_last_result'
  activerecord (3.2.12) lib/active_record/connection_adapters/postgresql_adapter.rb:1166:in `exec_cache'

The table was working fine until now (saved about 50 records before the error today). After opening the pg table in pgadmin. we found the id on the table is integer. We also found Id on other table is serial. It seems that the id should be serial so it can auto-increment. If it is, then how to convert a id column to serial from integer? If it is not, then how to fix this problem?

Answer 1

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).

If your column is an integer, you cannot convert it to serial, but you can mimic, what PostgreSQL would have done, just like you created your table with a serial:

CREATE SEQUENCE tablename_colname_seq;
ALTER TABLE tablename ALTER COLUMN colname SET DEFAULT nextval('tablename_colname_seq'::regclass);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Answer 2

I had an issue like this when working on a Rails 6 application.

I had a User model that is used to create a student and an admin:

class User < ApplicationRecord
  belongs_to :role
end

And a Role model:

class Role < ApplicationRecord
end

However, I wanted the admin and student roles to be assigned for each admin and student respectively when they are created without having a roles field in the forms, so my models were like this:

The Admin model:

class Admin < ApplicationRecord
  before_save :set_admin_role

  belongs_to :user

  private

  def set_admin_role
    # set role_id to '1' except if role_id is not empty
    user.role_id = '1' if user.role_id.nil?
  end
end

The Student model:

class Student < ApplicationRecord
  before_save :set_student_role

  belongs_to :user

  private

  def set_student_role
    # set role_id to '2' except if role_id is not empty
    user.role_id = '2' if user.role_id.nil?
  end
end

So this was throwing errors whenever I tried creating admins and students:

ActiveRecord::NotNullViolation (PG::NotNullViolation: ERROR:  null value in column "role_id" violates not-null constraint)

Here's how I solved it:

The issue was that the role_id in the users table was set to null: false in my migration file. I had to change it to null: true:

class CreateUsers < ActiveRecord::Migration[6.0]
  def change
    create_table :users do |t|
      t.string :email
      t.string :password_digest
      t.references :role, null: true, foreign_key: true

      t.timestamps
    end
  end
end

And then also changed the User model, to have the role_id as optional:

class User < ApplicationRecord
  belongs_to :role, optional: true
end

That's all.

I hope this helps

Answer 3

I had a very similar problem.

I'll tell what was wrong, maybe someone will find my experience useful

I had ERROR: null value in column "created_at" violates not-null constraint

and the problem was that I had a methods that ran in before_create and before_save and this methods tried to save with minor updates. So it tried to save few times.

My suggestion is to check what your code is doing in before_save and before_create