Composite primary key is not updated after saving
Here is a minimal test case that forms the basis of my question. Why, even if user
saved correctly, is the attribute user.id
not updated? Trying to re-find the record in the database retrieves it without issue and the attribute id
is set correctly.
AFAICT, this is not a matter of auto-incrementing a composite primary key in sqlite. The same problem occurs with the uuid / PostgreSQL combination. The schema has id
both a primary key with [ :account_id, :id ]
being a separate unique index.
#!/usr/bin/env ruby
gem "rails", "~> 5.0.2"
gem "composite_primary_keys"
require "active_record"
require "composite_primary_keys"
ActiveRecord::Base.establish_connection(
adapter: "sqlite3",
database: ":memory:"
)
ActiveRecord::Schema.define do
create_table :accounts, force: true do |t|
end
create_table :users, force: true do |t|
t.references :account
t.index [ :account_id, :id ], unique: true
end
end
class User < ActiveRecord::Base
self.primary_keys = [ :account_id, :id ]
belongs_to :account, inverse_of: :users
end
class Account < ActiveRecord::Base
has_many :users, inverse_of: :account
end
account = Account.create!
puts "created account: #{account.inspect}"
user = account.users.build
puts "before user.save: #{user.inspect}"
user.save
puts "after user.save: #{user.inspect}"
puts "account.users.first: #{account.users.first.inspect}"
And the result of running the script is:
~/src
frankjmattia@lappy-i686(ttys005)[4146] % ./cpk-test.rb
-- create_table(:accounts, {:force=>true})
-> 0.0036s
-- create_table(:users, {:force=>true})
-> 0.0009s
created account: #<Account id: 1>
before user.save: #<User id: nil, account_id: 1>
after user.save: #<User id: nil, account_id: 1>
account.users.first: #<User id: 1, account_id: 1>
Should user.id be [1,1]
after the first save? If this is a bug, who should I report it to?
source to share
As it turned out, the answer was simple. Rails usually gets the returned primary key from create and updates the model with it. The complex key does not reload on its own, so I have to do this. Mostly the logic from reload
in hook_out_create is used to retrieve the created record and update the attributes accordingly.
#!/usr/bin/env ruby
gem "rails", "5.0.2"
gem "composite_primary_keys", "9.0.6"
require "active_record"
require "composite_primary_keys"
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Schema.define do
create_table :accounts, force: true
create_table :users, force: true do |t|
t.integer :account_id, null: false
t.string :email, null: false
t.index [ :account_id, :id ], unique: true
t.index [ :account_id, :email ], unique: true
end
end
class User < ActiveRecord::Base
self.primary_keys = [ :account_id, :id ]
belongs_to :account, inverse_of: :users
after_create do
self.class.connection.clear_query_cache
fresh_person = self.class.unscoped {
self.class.find_by!(account: account, email: email)
}
@attributes = fresh_person.instance_variable_get('@attributes')
@new_record = false
self
end
end
class Account < ActiveRecord::Base
has_many :users, inverse_of: :account
end
account = Account.create!
user = account.users.build(email: "#{SecureRandom.hex(4)}@example.com")
puts "before save user: #{user.inspect}"
user.save
puts "after save user: #{user.inspect}"
And now:
frankjmattia@lappy-i686(ttys003)[4108] % ./cpk-test.rb
-- create_table(:accounts, {:force=>true})
-> 0.0045s
-- create_table(:users, {:force=>true})
-> 0.0009s
before save user: #<User id: nil, account_id: 1, email: "a54c2385@example.com">
after save user: #<User id: 1, account_id: 1, email: "a54c2385@example.com">
source to share
SQLite does not support automatic addition to a composite primary key. You can find related questions at SO: 1 , 2 .
Here's @SingleNegationElimination's answer from the second link:
In sqlite, you only get auto-increment behavior when only one integer column is the primary key. compound keys prevent autoincrement from taking effect.
You can get a similar result by specifying id as the only primary key, but then adding an additional unique constraint on id, col3.
And it composite_primary_keys
keeps this logic.
There is also a trick for this: sqlite: multi-column primary key with auto-increment column
source to share