SQLite3::ConstraintException: column email is not unique

Question

I'm absolutely new to Rails, I nearly do not know what I am doing. But. The problem is: signing up new user with Devise results in:

SQLite3::ConstraintException: column email is not unique: 
INSERT INTO "users" ("created_at","encrypted_password", "name", "updated_at") 
VALUES (?, ?, ?, ?)

And the request parameters:

 {"utf8"=>"✓",
 "authenticity_token"=>"1bgk4ovS3JitphVkIvcCZi3ex8QsBq4eEf6ZihQLiHg=",
 "user"=>{"name"=>"Someone",
 "email"=>"8@prosto.me",
 "password"=>"[FILTERED]"},
 "commit"=>"Sign up"}

User model:

class User < ActiveRecord::Base
  # Include default devise modules. Others available are:
  # :token_authenticatable, :confirmable,
  # :lockable, :timeoutable and :omniauthable
  devise :database_authenticatable, :registerable,
         :recoverable, :rememberable, :trackable;
end

DB migration:

class DeviseCreateUsers < ActiveRecord::Migration
  def self.up
    change_table(:users) do |t|
      ## Database authenticatable
      t.string :email,              :null => false, :default => ""
      t.string :name,               :null => false, :default => ""
      t.string :encrypted_password, :null => false, :default => ""

      ## Recoverable
      t.string   :reset_password_token
      t.datetime :reset_password_sent_at

      ## Rememberable
      t.datetime :remember_created_at

      ## Trackable
      t.integer  :sign_in_count, :default => 0
      t.datetime :current_sign_in_at
      t.datetime :last_sign_in_at
      t.string   :current_sign_in_ip
      t.string   :last_sign_in_ip

      ## Confirmable
      # t.string   :confirmation_token
      # t.datetime :confirmed_at
      # t.datetime :confirmation_sent_at
      # t.string   :unconfirmed_email # Only if using reconfirmable

      ## Lockable
      # t.integer  :failed_attempts, :default => 0 # Only if lock strategy is :failed_attempts
      # t.string   :unlock_token # Only if unlock strategy is :email or :both
      # t.datetime :locked_at

      ## Token authenticatable
      # t.string :authentication_token


      # Uncomment below if timestamps were not included in your original model.
      # t.timestamps
    end

    add_index :users, :email,                :unique => true
    add_index :users, :name,                 :unique => true
    add_index :users, :reset_password_token, :unique => true
    # add_index :users, :confirmation_token,   :unique => true
    # add_index :users, :unlock_token,         :unique => true
    # add_index :users, :authentication_token, :unique => true
  end

  def self.down
    # By default, we don't want to make any assumption about how to roll back a migration when your
    # model already existed. Please edit below which fields you would like to remove in this migration.
  end
end

Please tell me if I need to provide any other code. And thank you for all your help in advance.

Update with DB schema:

ActiveRecord::Schema.define(version: 20131012114812) do

  create_table "users", force: true do |t|
    t.string   "email",                  default: "", null: false
    t.string   "encrypted_password",     default: "", null: false
    t.string   "reset_password_token"
    t.datetime "reset_password_sent_at"
    t.datetime "remember_created_at"
    t.integer  "sign_in_count",          default: 0,  null: false
    t.datetime "current_sign_in_at"
    t.datetime "last_sign_in_at"
    t.string   "current_sign_in_ip"
    t.string   "last_sign_in_ip"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.string   "name"
  end

  add_index "users", ["email"], name: "index_users_on_email", unique: true
  add_index "users", ["reset_password_token"], name: "index_users_on_reset_password_token", unique: true

end

Update 2: And there is also a problem with authentication. Devise tells 'Invalid email or password' for any previously successfully signed up user in attempt to login.

Looks like you're having this same issue: http://stackoverflow.com/questions/5769758/adding-index-to-email-column-culprit-for-unit-tests-failing — Dave, Oct 14 '13 at 16:31
Hm. Try to [drop](http://stackoverflow.com/questions/4020131/rails-db-migration-how-to-drop-a-table) a users table. This is a thing that you should **not** do, but it might help you. — user2422869, Oct 14 '13 at 16:31
@Dave Devise worked perfectly after the install. So that's not the case I believe. — Vladislav Arbatov, Oct 14 '13 at 17:00
Have you tried with another email, which might have more than just a number as the prefix? That could be a possible cause — Richard Peck, Oct 14 '13 at 18:16

sealocal · Answer 1 · 2014-01-26T23:37:14.837

SQLite is telling you that it's trying to create a new record, where one of the values will be an email, but there is already an existing record with that email.

An easy way to read your database records is query the DB in a terminal window:

$ rails console
$ User.all

If you want to see your test DB, which will be loaded with your fixtures:

$ rails console -e=test
$ User.all

Look for records that have the same email of the one you're trying to create.

If this is your first time using Devise, you should check your fixtures. Devise currently defaults to two fixtures that have no attributes. If you're running in a test environment then those fixtures will be loaded into the test DB as a two records with nil values for email, which are duplicate email values. Fixtures like the ones below will get you passed your error message.

file: app/test/fixtures/users.yml

one:
  email: user@example.com
  encrypted_password: password1

two:
  email: user2@example.com
  encrypted_password: password2

jvperrin · Answer 2 · 2013-10-14T16:32:42.400

1

Try adding a uniqueness validation to your User model:

validates_uniqueness_of :email, :allow_blank => true

This will re-render your user creation form instead of causing an error.

edited Oct 14 '13 at 16:32

answered Oct 14 '13 at 16:25

jvperrin

3,368
1
23
33

Tried that. Devise throws "Email has already been taken" error. The email provided is absolutely new to the application of course. – Vladislav Arbatov Oct 14 '13 at 16:28
@VladislavIvanov I think the problem you are encountering is that it is ensuring email uniqueness, but if you want the email to have a blank default value, you will have to use `:allow_blank => true` to accept blank values. See my updated answer above and hopefully that works for you. – jvperrin Oct 14 '13 at 16:32
Thank you for the advice, but now I get the app error 'SQLite3::ConstraintException: column email is not unique: INSERT INTO "users" ("created_at", "encrypted_password", "name", "updated_at") VALUES (?, ?, ?, ?)' – Vladislav Arbatov Oct 14 '13 at 16:43
@VladislavIvanov Could you post the data in your users table in the question? I think there might be some data that is messing up any new saves. – jvperrin Oct 14 '13 at 16:46
Soooo how do I do that? :-) – Vladislav Arbatov Oct 14 '13 at 16:59

score 1 · Answer 3 · answered Oct 14 '13 at 16:28

1

Do you have any other "email" columns in that database?

Perhaps you already had a "users" table, where the email column has been replicated with Devise. It would be helpful if you could show us which columns your table has :)

answered Oct 14 '13 at 16:28

Richard Peck

76,116
9
93
147

1

SQLite3::ConstraintException: column email is not unique: INSERT INTO "users" ("created_at","encrypted_password", "name", "updated_at") VALUES (?, ?, ?, ?) -> looks like you're not inserting an email with this query? Could that be the problem? – Richard Peck Oct 14 '13 at 17:04
How do I check that? I'm a real newby by the way :-) – Vladislav Arbatov Oct 14 '13 at 17:13
How did you get the SQL dump you posted at the top of your post? I was under the impression you sent data through the system in a test, but it seems you just signed up to your app through the devise form? – Richard Peck Oct 14 '13 at 17:19
Hmmm this is quite perplexing! Have you got any logs you could share? You can get them by going to /log/development.log – Richard Peck Oct 14 '13 at 18:16
OK this is something I cannot explain. I restarted the server several times (which I did at least 5 times before) and the signup and login somehow properly works. I don't get it. – Vladislav Arbatov Oct 14 '13 at 18:34
This may be but the <%= current_user.name %> stopped working suddenly. It supposed to output users name but shows blank space instead... – Vladislav Arbatov Oct 14 '13 at 18:43
This is weird -- do you have any github repo we could look at? – Richard Peck Oct 14 '13 at 18:44
Thanks! Got a database dump too? – Richard Peck Oct 14 '13 at 18:50
In the dump I found that all the users don't have a name (have NULL instead). The only one has a name but the email for this user is empty. – Vladislav Arbatov Oct 14 '13 at 19:34
Should I drop the db and migrate again? Would it help? – Vladislav Arbatov Oct 14 '13 at 19:38
Well firstly your current_user.name error is, from what it seems, caused by devise not having a name field for when you sign up. I would take out the reference to current_user.name and see if it works first :) – Richard Peck Oct 14 '13 at 21:21

SQLite3::ConstraintException: column email is not unique

3 Answers3