Rails: attempting save an existiing row but rails generates sql insert instead of update

Question

The error

When I have a new record, I don't have a problem.

When I have an existing record I'm trying to update, an exception (see below) is thrown.

The following two variants of Rails code both throw the same exception when I have an existing row in my email_addresses table I wish to update:

ret = @email_address.update(email_address_id: @email_address.email_address_id)

I have also tried

ret = @email_address.save

and get the same exception.

The exception

When I have an existing record I wish to update, the error I'm getting is

#<ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "email_addresses_pkey"
DETAIL:  Key (email_address_id)=(2651) already exists.
: INSERT INTO "email_addresses" ("email_address_id", "email_address", "zipcode", "confirm_token") VALUES ($1, $2, $3, $4) RETURNING "email_address_id">

Where I think the problem is

I think the problem may be related to my not having an id column.

email_address_id serves the same purpose as an id column.

Background

Using psql's d+ command I get

development=# d+ email_addresses
                                                                          Table "public.email_addresses"
       Column       |       Type        |                                 Modifiers                                  | Storage  | Stats target |            Description            
--------------------+-------------------+----------------------------------------------------------------------------+----------+--------------+-----------------------------------
 email_address_id   | integer           | not null default nextval('email_addresses_email_address_id_seq'::regclass) | plain    |              | 
 email_address      | citext            | not null                                                                   | extended |              | 
 unsubscribe_reason | text              | not null default ''::text                                                  | extended |              | 
 zipcode            | text              |                                                                            | extended |              | If non-NULL then check confirmed.
 email_confirmed    | boolean           | default false                                                              | plain    |              | 
 confirm_token      | character varying |                                                                            | extended |              | 
Indexes:
    "email_addresses_pkey" PRIMARY KEY, btree (email_address_id)
    "email_address_idx" UNIQUE, btree (email_address)
Referenced by:
    TABLE "xref__email_addresses__realtor_organizations" CONSTRAINT "email_address_id_fkey" FOREIGN KEY (email_address_id) REFERENCES email_addresses(email_address_id) ON UPDATE CASCADE ON DELETE RESTRICT

Model

class EmailAddress < ApplicationRecord
  validates_presence_of :email_address
  validates_format_of :email_address, with: /A([^@s]+)@((?:[-a-z0-9]+.)+[a-z]{2,})/i, on: :create
  validates_format_of :email_address, with: /A([^@s]+)@((?:[-a-z0-9]+.)+[a-z]{2,})/i, on: :save
  validates_format_of :email_address, with: /A([^@s]+)@((?:[-a-z0-9]+.)+[a-z]{2,})/i, on: :update

  def initialize(email_address_params)
    super(email_address_params)
    confirmation_token()
  end


  # zipcodes can be nil/null in the database but we should never allow a human user to create such a record.
  validates_presence_of :zipcode
  validates_format_of :zipcode, with: /Ad{5}(-d{4})?z/, message: "zipcode should be in the form 12345", on: :create

  def email_activate
    self.email_confirmed = true
    self.confirm_token = nil
    save!(:validate => false)
  end

  private
  def confirmation_token
    if self.confirm_token.blank?
      self.confirm_token = SecureRandom.urlsafe_base64.to_s
    end
  end
end

Generated sql code:

Rails generates:

INSERT INTO "email_addresses" ("email_address_id", "email_address", "zipcode", "confirm_token") VALUES ($1, $2, $3, $4) RETURNING "email_address_id"  [["email_address_id", 2651], ["email_address", "[email protected]"], ["zipcode", "80503"], ["confirm_token", "r-UX4zOdOmHC7lO7Ta2pBg"]]

This will, obviously, fail if email_address_id already exists in the email_addresses table because of the

"email_addresses_pkey" PRIMARY KEY, btree (email_address_id)

implicit constraint on primary keys.

I can probably get this to work by writing SQL code directly in Rails but I'd like to keep with Rails code as well as understanding what I'm doing wrong.

I have searched the web for several hours without success. I've tried tracing through the rails code but it got so complicated with metaprogramming code that I got lost.

Questions

How does Rails know when to do an SQL UPDATE rather than an SQL INSERT?

How do I do I update an existing record given my existing email_addresses table?

Answer 1

Since you are using a non standard id, you need to tell rails the column to use as a primary_key, this way it can evaluate whether a save should insert or update.

To do so you need to add self.primary_key = 'email_address_id' in your model.

Remember Rails favors convention over configuration, so anytime you break convention you need to tell Rails.

P.S.: it seems redundant having the pkey for email_addresses named email_address_id; usually the format of resource_id is conventionally used for fkeys, while for pkeys you can just define the column name.