# Autoincrement on Join Table not working

**URL:** <https://www.ruby-forum.com/t/autoincrement-on-join-table-not-working/82273>\
**Category:** Rails\
**Created:** [January 18, 2007, 5:48pm UTC](https://www.ruby-forum.com/t/autoincrement-on-join-table-not-working/82273 "2007-01-18T17:48:50Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![dnorton](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/b3f665/32.png) [@dnorton](https://www.ruby-forum.com/u/dnorton)\
**Post date:** [January 18, 2007, 5:48pm UTC](https://www.ruby-forum.com/t/autoincrement-on-join-table-not-working/82273/1 "2007-01-18T17:48:50Z")

</div>

Hi all, I’m trying to create a directed may to many relationship  
against a single table as seen here:  
[http://wiki.rubyonrails.com/rails/pages/HowToUseManyToManyAgainstASingleTable](http://wiki.rubyonrails.com/rails/pages/HowToUseManyToManyAgainstASingleTable)

I’m running 1.2RC2 right now and am doing this on a Postgresql 8.2  
database.

I’ve defined my model like this:

class Item \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :tags,  
:class\_name =\> ‘Item’,  
:join\_table =\> ‘items\_items’,  
:foreign\_key =\> “parent\_id”,  
:association\_foreign\_key =\> “child\_id”

has\_and\_belongs\_to\_many :tasks,  
:class\_name =\> ‘Item’,  
:join\_table =\> ‘items\_items’,  
:foreign\_key =\> “child\_id”,  
:association\_foreign\_key =\> “parent\_id”

validates\_presence\_of :name  
end

Then, I try it out in the console:

phone\_context = Item.new(:name =\> “phone”)  
phone\_context.save  
=\> true  
email\_context = Item.new(:name =\> “email”)  
email\_context.save  
=\> true  
alice\_task = Item.new(:name =\> “speak with alice about account”)  
alice\_task.save  
=\> true  
email\_context.tasks \<\< alice\_task

=\> [#\<Item:0x32e4e30 @attributes={“end\_date”=\>nil, “start\_date”=\>nil,  
“name”=\>“speak with alice about account”, “done”=\>false,  
“updated\_on”=\>Thu Jan 18 11:31:37 -0500 2007, “id”=\>26, “reset”=\>nil,  
“due”=\>false, “end\_time”=\>nil, “start\_time”=\>nil},  
errors#\<ActiveRecord::Errors:0x32dc514 @errors={},  
@base=#\<Item:0x32e4e30 …\>, new\_recordfalse,  
new\_record\_before\_savetrue]

email\_context.tasks[0].name  
=\> “speak with alice about account”  
alice\_task.tags[0].name  
=\> “email”

So far so good. Now, since I have Alice’s phone number too, I decide  
to add the alice\_task to the phone\_context tasks.

phone\_context.tasks \<\< alice\_task  
ActiveRecord::StatementInvalid: RuntimeError: ERROR C23505  
Mduplicate key violates unique constraint “items\_items\_pkey”  
Fnbtinsert.c L277 R\_bt\_check\_unique: INSERT INTO items\_items  
(“id”, “child\_id”, “parent\_id”) VALUES (26, 24, 26)  
from  
./script/…/config/…/config/…/vendor/rails/activerecord/lib/active\_record/connection\_adapters/abstract\_adapter.rb:128:in  
`log' from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:152:in `execute’  
from  
./script/…/config/…/config/…/vendor/rails/activerecord/lib/active\_record/associations/has\_and\_belongs\_to\_many\_association.rb:132:in  
`insert_record' from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/associations/association_collection.rb:26:in `\<\<’  
from  
./script/…/config/…/config/…/vendor/rails/activerecord/lib/active\_record/associations/association\_collection.rb:23:in  
`each' from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/associations/association_collection.rb:23:in `\<\<’  
from  
./script/…/config/…/config/…/vendor/rails/activerecord/lib/active\_record/connection\_adapters/abstract/database\_statements.rb:59:in  
`transaction' from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/transactions.rb:95:in `transaction’  
from  
./script/…/config/…/config/…/vendor/rails/activerecord/lib/active\_record/transactions.rb:121:in  
`transaction' from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/associations/association_collection.rb:22:in `\<\<’  
from (irb):9

When I run “select \* from items\_items\_id\_seq” on my database, I get this  
back:

sequence\_name | last\_value | increment\_by | max\_value  
| min\_value | cache\_value | log\_cnt | is\_cycled | is\_called  
--------------------±-----------±-------------±--------------------±----------±------------±--------±----------±----------  
items\_items\_id\_seq | 1 | 1 | 9223372036854775807  
| 1 | 1 | 1 | f | f  
(1 row)

So it appears that Rails isn’t using the autoincrement for the  
items\_items table (a setting I missed with the habtm declarations?)

Also, seemingly in relation to this error, when I inspect the tags of  
alice\_task, I get this

alice\_task.tags[0]  
=\> #\<Item:0x32a32c8 @attributes={“end\_date”=\>nil, “start\_date”=\>nil,  
_ **“name”=\>“email”** _, “done”=\>“f”, “updated\_on”=\>“2007-01-18”,  
_ **“id”=\>“26”** _, “child\_id”=\>“25”, “reset”=\>nil, “parent\_id”=\>“26”,  
“due”=\>“f”, “end\_time”=\>nil, “start\_time”=\>nil}, readonlytrue

But I can easily see that the email\_context’s id is not 26:  
email\_context.id  
=\> 25

If anyone has any suggestions as to what I’m doing wrong or if there’s  
another approach that fits better with 1.2 or if you can help me  
identify this as a rails bug (and that I’m not going crazy), I would  
be very appreciative.

–  
-Dan Nugent

---

<div class="post-metadata">

**Author:** ![dnorton](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/b3f665/32.png) [@dnorton](https://www.ruby-forum.com/u/dnorton)\
**Post date:** [January 19, 2007, 4:30pm UTC](https://www.ruby-forum.com/t/autoincrement-on-join-table-not-working/82273/2 "2007-01-19T16:30:18Z")

</div>

I guess what I was doing was wrong, Here’s the updated model for  
anyone that’s curious.

class Item \< ActiveRecord::Base  
has\_many :notes  
has\_and\_belongs\_to\_many :tags,  
:class\_name =\> ‘Item’,  
:join\_table =\> ‘items\_items’,  
:foreign\_key =\> “parent\_id”,  
:association\_foreign\_key =\> “child\_id”,  
:select =\> “items.\*”,  
:insert\_sql =\> ‘INSERT INTO items\_items (“child\_id”, “parent\_id”)  
VALUES (#{record.id}, #{id})’

has\_and\_belongs\_to\_many :tasks,  
:class\_name =\> ‘Item’,  
:join\_table =\> ‘items\_items’,  
:foreign\_key =\> “child\_id”,  
:association\_foreign\_key =\> “parent\_id”,  
:select =\> “items.\*”,  
:insert\_sql =\> ‘INSERT INTO items\_items (“child\_id”, “parent\_id”)  
VALUES (#{id}, #{record.id})’

validates\_presence\_of :name  
end

What I was not aware of was that Rails would attempt to populate the  
records found through the join with the attributes of the join table  
(not something I particularly see the value of) and in so doing  
overwrites the accessor for the real ids of the found records.  
Further, and this one I can’t explain, the default insert statement  
wants to make the association foreign key the primary key of the  
record of the join table.

Is this stuff really intended to work this way?

On 1/18/07, Daniel N. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> ```
> :class_name => 'Item',
> 
> ```
> 
> validates\_presence\_of :name  
> alice\_task = Item.new(:name =\> “speak with alice about account”)  
> new\_record\_before\_savetrue]  
> ActiveRecord::StatementInvalid: RuntimeError: ERROR C23505  
> `\<\<’  
> from ./script/…/config/…/config/…/vendor/rails/activerecord/lib/active\_record/associations/association\_collection.rb:22:in  
> (1 row)  
> _ **“id”=\>“26”** _, “child\_id”=\>“25”, “reset”=\>nil, “parent\_id”=\>“26”,
> 
> –  
> -Dan Nugent

–  
-Dan Nugent