composite primary key not updating after save

Question

Here is a minimal test case that forms the basis of my question. Why is it that even though user is properly saved, the attribute user.id isn't updated? Attempting to re-find the record in the database fetches it without issue and the id attribute is properly set.

AFAICT, this is not a matter of trying to auto-increment a composite primary key in sqlite. The same issue occurs with the uuid/PostgreSQL combination as well. The schema only has id as the 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 that script is:

~/src
% ./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>

Shouldn't user.id be [1,1] after the first save? If this is a bug, who should I report it to?

I just wanted to give a thumbs up for actually giving a working example in a single file, I wish more questions were this focused. — Eyeslandic, Apr 09 '17 at 08:40
Thank you but much thanks is due to Jon Leighton for writing this excellent post on it. http://www.jonathanleighton.com/articles/2011/awesome-active-record-bug-reports/ — Frank Joseph Mattia, Apr 09 '17 at 22:43

Frank Joseph Mattia · Accepted Answer · 2021-03-13T16:31:09.113

As it turns out, the answer was a simple one. Rails normally gets the returned primary key from the create and updates the model with it. The composite key doesn't reload on its own so I have to do it. Basically used the logic from reload in an after_create hook to fetch 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:

% ./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">

score 0 · Answer 2 · edited May 23 '17 at 11:54

0

SQLite does not support auto increment on composite primary key. You may find related questions in SO: 1, 2.

Here an @SingleNegationElimination answer from second link:

In sqlite, you only get autoincrement behavior when only one integer column is the primary key. composite keys prevent autoincrement from taking effect.

You can get a similar result by defining id as the only primary key, but then adding an additional unique constraint on id, col3.

And composite_primary_keys keeps that logic.

Also here exists trick to do so: sqlite: multi-column primary key with an auto increment column

edited May 23 '17 at 11:54

Community

1
1

answered Apr 09 '17 at 04:20

Ilya Lavrov

2,810
3
20
37

I am only using sqlite for ease of demonstrating the issue. I get the same results with UUID and PostgreSQL. The record saves fine and the id is ultimately correct but the instance that `save` is called on isn't updated to reflect the new id after the save is completed. – Frank Joseph Mattia Apr 09 '17 at 04:31

composite primary key not updating after save

2 Answers2