1

I have taken over an existing Rails 3.2.9 app and am trying to set it up on my local windows 7 machine, but am getting the following error when trying to register a user:

ActiveRecord::StatementInvalid in Workers::RegistrationsController#create

PG::Error: ERROR: null value in column "id" violates not-null constraint DETAIL:

Failing row contains (null, 8f9f248b33a121ec83a38310, ZqddTnkfmB7Afu9Ukwcx, 0, null, null, null, null, null, 2014-11-25 17:55:43.8317, 2014-11-25 17:55:43.8317, null, dtest@gmail.com, null, null, null, null, null, null, null, null, null, null, null, pending, donnie, bertz, null, f, f, f, null, null, null, null, null, null, test.com, yEqp4FV5XCKS9xaRnnwz, null, 2014-11-25 17:55:43.8307, null, null, null, 0, null, null, Work, null, null, null, null, null, f, null, f, null, 0, 0, 0, null, f, 0, null, null, null, null, null). :

INSERT INTO "users" ("active", "keys", "address", "admin", "avail", "availof", "content_type", "file_name", "file_size", "a_updated_at", "calendar_json", "employees", "company_1", "company_2", "company_3", "confirmation_sent_at", "confirmation_token", "confirmed_at", "created_at", "current_sign_in_at", "current_sign_in_ip", "data", "domain", "email", "encrypted_password", "extra_links", "f1", "failed_attempts", "first_name", "fulltime", "general_help", "headline", "intro", "jobs_count", "keywords", "last_name", "last_request_at", "last_sign_in_at", "last_sign_in_ip", "l1", "locked_at", "metric_units", "opt_out", "password_salt", "phone", "credit", "rate", "rating_count", "receive_correspondence", "remember_created_at", "remember_token", "remote", "reset_password_sent_at", "reset_password_token", "sign_in_count", "avatar_url", "status", "time_zone", "t1", "type", "unconfirmed_email", "unlock_token", "updated_at", "used_credits", "video_url", "wizard_step", "zipcode") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67) RETURNING "id"

Originally, I was getting an ActiveRecord::NotNullViolation error and the :id field was being included in the insert statement. To try to resolve that, I added self.primary_key = :id to the user model. That removed :id from the insert, but I now notice that, while the insert statement looks ok in the error message, the 'failing row' actually has one extra column (68) and they are not in the same order as listed in the insert statement (which lists the columns alphabetically).

The application is utilizing devise and omniauth, if either of those would be relevant.

schema.rb:

create_table "users", :force => true do |t|
    t.string   "encrypted_password",                                 :null => false
    t.string   "password_salt"
    t.integer  "sign_in_count",          :default => 0,              :null => false
    t.datetime "last_request_at"
    t.datetime "last_sign_in_at" 
    .
    .
    . 
    t.string   "unconfirmed_email"   
end

EDIT 1

SQL:

CREATE TABLE users (
id integer NOT NULL,
created_at timestamp without time zone,
updated_at timestamp without time zone,
encrypted_password character varying(255) NOT NULL,
password_salt character varying(255),
sign_in_count integer DEFAULT 0 NOT NULL,
last_request_at timestamp without time zone,
last_sign_in_at timestamp without time zone,
current_sign_in_at timestamp without time zone,
last_sign_in_ip character varying(255),
current_sign_in_ip character varying(255),
email character varying(255) NOT NULL,
.
.
.
reset_password_sent_at timestamp without time zone,
unconfirmed_email character varying(255),
data hstore
);

ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);

I should also mention that the only instructions from the original developers were to manually create the Users table first, for ActiveAdmin issues.

EDIT 2 - the 'offending' code

class Employers::RegistrationsController < Devise::RegistrationsController
  def create
    super
    session[:omniauth] = nil unless @employer.new_record?
  end

  private

  def build_resource(*args)
    super
    if session[:omniauth]
      @employer.apply_omniauth(session[:omniauth])
    end
  end

  #Overwrite signin to make sure only users are signed in (for backward compatibility)
  def sign_in(resource_or_scope, *args)
    super(:user, *args)
  end

end
daz13
  • 245
  • 2
  • 15
  • Could you show us your table definition? (The `CREATE TABLE` sql statement, not just your ruby migration) – pozs Jan 27 '15 at 10:17
  • @pozs - extra details added – daz13 Jan 28 '15 at 14:29
  • Thanks, it's really strange. Rails should generate an *auto-increment* like `id` column (in case of Postgres it should typed as a `serial` instead of `int` -- or at least it should has a default value, which is the nextval of a sequence) -- from your `CREATE TABLE` it seems that's not the case, that's why you must supply `id` in your `INSERT` statements, or it will violate the not-null constraint. – pozs Jan 28 '15 at 16:23
  • 1
    It looks like the `id` is not being defined properly within Postgres as a `serial primary key`. Without it being declared as an auto-incrementing key, it's value must be provided on `insert` statements. Removing the `id` from the SQL was move in the wrong direction. You wrote (above) that the original error was `ActiveRecord::NotNullViolation`, but you did not say if the error indicated which field was causing the violation. Perhaps you can elaborate the original error? See this for reference: http://stackoverflow.com/questions/7718585/how-to-set-auto-increment-primary-key-in-postgresql – aks Feb 02 '15 at 23:15
  • @aks - Thanks for help. In the end, your comment regarding the original error prompted me to return to the start and this time around I found the proper steps in the proper order and got it all up and running - as originally coded, without any adjustments. Cheers. – daz13 Feb 13 '15 at 06:05

1 Answers1

0

You need to write an alter table migration.

First, generate the migration

$ rails g migration add_uniqueness_to_users

Open up your migration file, and do something like this therein:

class AddUniquenessToUsers < ActiveRecord::Migration
  def up
    change_column(:events, :id, 'SERIAL PRIMARY KEY')
    add_index :events, :id, :unique => true
  end
  def down
    remove_index :events, :id
    change_column(:events, :id, :integer)
  end
end

This accomplishes everything you need, updating the column type to SERIAL PRIMARY KEY, which is what is necessary for incremental updates to the column, adds a unique constraint on the column, and ensures that if you were to run the migration in the other direction, the reverse happens appropriately.

zealoushacker
  • 6,766
  • 5
  • 35
  • 44