Strange HABTM behavior against SQL Server (AR 1.14.4)

Rails
1

All,

Win XP
SQL Server 2000
Rails 1.1.6 (AR 1.14.4)

I have two objects (Job and Warning) in a HABTM relationship. Below are
the pertinent sections of the model files and the schema for the join
table.

job.rb:
class Job < ActiveRecord::Base
has_and_belongs_to_many :warnings, :foreign_key =>
‘JobReferenceNumber’
set_table_name :JobData
set_primary_key :JobReferenceNumber

warning.rb:
class Warning < ActiveRecord::Base
has_and_belongs_to_many :jobs, :association_foreign_key =>
‘JobReferenceNumber’

jobs_warnings table migration:
create_table :jobs_warnings do |t|
t.column :JobReferenceNumber, :integer, :null => false
t.column :warning_id, :integer, :null => false
end

What I find in both my app. and ruby script/console is the following:

If I run the following three statements against a saved Job object j
with JobReferenceNumber 550, and an empty jobs_warnings table:

j.warnings << Warning.find(3)
j.warnings << Warning.find(2)
j.warnings << Warning.find(1)

I end up seeing rows in the jobs_warnings table with

id: 3
JobReferenceNumber: 550
warning_id: 3

id: 2
JobReferenceNumber: 550
warning_id: 2

id: 1
JobReferenceNumber: 550
warning_id: 1

This is wrong. The id value is equal to the warning_id value. The id
columns in jobs_warnings should start at 1 since id is an identity
column. And, since there are three distinct warnings, the 4th insert
fails with a duplicate key failure.

Here is a piece of the development log from running my app:
SQL (0.080000) SET IDENTITY_INSERT jobs_warnings ON
SQL (0.080000) SELECT @@IDENTITY AS Ident
SQL (0.080000) INSERT INTO jobs_warnings ([JobReferenceNumber], [id],
[warning_id]) VALUES (551, 3, 3)

I believe the id from the warnings table is being selected and then used
as the id value for the join table. Is this a bug in the SQL Server
driver when using HABTM relationships with non-standard table names?

Does anyone know of any problems with HABTM against SQL Server that
would explain these erroneous join table INSERTS?

Thanks,
Wes

2

Wes G. wrote:

Does anyone know of any problems with HABTM against SQL Server that
would explain these erroneous join table INSERTS?

Yup…I ran into the exact same thing a while back. It’s a “Feature” or
so I was told. I was also told I was wrong in my approach to solving my
issue. I didn’t agree so I removed the ID field from the join table
(You don’t really need it.) and my stuff works fine. Your mileage may
vary.

Enjoy!

MudDawg

3

Steve K. wrote:

Yup…I ran into the exact same thing a while back. It’s a “Feature” or
so I was told. I was also told I was wrong in my approach to solving my
issue. I didn’t agree so I removed the ID field from the join table
(You don’t really need it.) and my stuff works fine. Your mileage may
vary.

Steve,

Thanks for this. Something strange - in the case I outlined above, the
“first” of the two joined tables (table “JobData” for class “job”) is a
legacy table, and the second follows the Rails convention. I have
another pair of tables where the “first” table uses the “normal” Rails
convention and the second one is the legacy table, and that works just
fine.

I have also noticed that for some reason, you need to use strings
instead of symbols for the values of :foreign_key and
:association_foreign_key. I wonder if that’s SQL Server specific as
well.

Glad to know I’m not crazy.

Wes

4

I decided to go the has_many :though route, so I created a join model
named JobsWarning and then set up the relationships like so:

Job (job.rb):
has_many :jobs_warnings, :foreign_key => :JobReferenceNumber,
:dependent => :delete_all
has_many :warnings, :through => :jobs_warnings

Warning (warning.rb):
has_many :jobs_warnings
has_many :jobs, :through => :jobs_warnings

JobsWarning (jobs_warning.rb):
belongs_to :job, :foreign_key => :JobReferenceNumber
belongs_to :warning

Given that my job object is saved, when I do job.warnings <<
Warning.find(x), I expect that records will automatically be saved (like
they are for HABTM). But these join records are not automatically
saved.

Is this correct?

Thanks,
Wes

5

Well, this is more explicit, but it seems to work (code is in job.rb):

self.jobs_warnings.clear
self.jobs_warnings.create(:JobReferenceNumber =>
self.JobReferenceNumber,
:warning_id =>
Warning.find_by_title(“Javascript”).id)

whereas self.jobs << Warning.find(x) simply adds the warning object to
the collection in memory.

Wes