Rails:尝试保存现有行，但 Rails 生成 sql 插入而不是更新 [英] Rails: attempting save an existiing row but rails generates sql insert instead of update
问题描述
当我有新记录时，我没有问题.
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.
当我希望更新 email_addresses 表中的现有行时，以下两个 Rails 代码变体都抛出相同的异常:
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)
我也试过
ret = @email_address.save
并得到相同的异常.
当我有想要更新的现有记录时，我得到的错误是
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">
我认为问题出在哪里
我认为问题可能与我没有 id 列有关.
Where I think the problem is
I think the problem may be related to my not having an id column.
email_address_id 与 id 列的用途相同.
email_address_id serves the same purpose as an id column.
使用 psql 的 \d+ 命令我得到
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
型号
class EmailAddress < ApplicationRecord
validates_presence_of :email_address
validates_format_of :email_address, with: /\A([^@\s]+)@((?:[-a-z0-9]+\.)+[a-z]{2,})\Z/i, on: :create
validates_format_of :email_address, with: /\A([^@\s]+)@((?:[-a-z0-9]+\.)+[a-z]{2,})\Z/i, on: :save
validates_format_of :email_address, with: /\A([^@\s]+)@((?:[-a-z0-9]+\.)+[a-z]{2,})\Z/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: /\A\d{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
生成的sql代码:
Rails 生成:
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", "ralphs@dos32.com"], ["zipcode", "80503"], ["confirm_token", "r-UX4zOdOmHC7lO7Ta2pBg"]]
如果 email_address_id 已经存在于 email_addresses 表中，这显然会失败，因为
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)
对主键的隐式约束.
我可能可以通过直接在 Rails 中编写 SQL 代码来实现这一点，但我想继续使用 Rails 代码并了解我做错了什么.
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.
我在网上搜索了几个小时都没有成功.我尝试过跟踪 rails 代码，但是元编程代码太复杂了，以至于我迷路了.
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.
Rails 如何知道何时执行 SQL UPDATE 而不是 SQL INSERT?
How does Rails know when to do an SQL UPDATE rather than an SQL INSERT?
如何根据我现有的 email_addresses 表更新现有记录?
How do I do I update an existing record given my existing email_addresses table?
推荐答案
由于您使用的是非标准 id，您需要告诉 rails 该列用作 primary_key，这样它可以评估是否应该插入或保存更新.
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.
为此，您需要在模型中添加 self.primary_key = 'email_address_id'.
To do so you need to add self.primary_key = 'email_address_id' in your model.
请记住，Rails 更喜欢约定俗成的配置，因此无论何时您违反约定，您都需要告诉 Rails.
Remember Rails favors convention over configuration, so anytime you break convention you need to tell Rails.
P.S.:将 email_addresses 的 pkey 命名为 email_address_id 似乎是多余的；通常，resource_id 的格式通常用于 fkeys，而对于 pkeys，您可以只定义列名.
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.
这篇关于Rails:尝试保存现有行，但 Rails 生成 sql 插入而不是更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
