# Migration and Mysql row ID

**URL:** <https://www.ruby-forum.com/t/migration-and-mysql-row-id/99237>\
**Category:** Rails\
**Created:** [July 21, 2006, 8:06am UTC](https://www.ruby-forum.com/t/migration-and-mysql-row-id/99237 "2006-07-21T08:06:31Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![xia](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/x/bb73d2/32.png) [@xia](https://www.ruby-forum.com/u/xia)\
**Post date:** [July 21, 2006, 8:06am UTC](https://www.ruby-forum.com/t/migration-and-mysql-row-id/99237/1 "2006-07-21T08:06:31Z")

</div>

Apologies if this has been discussed before, but with the search  
function down its hard to find out if it has

The first Agile book recommended using the automatically assigned row id  
of an object as a foreign key reference, as rails would automatically  
interpret the attribute (for example) product\_id as the table ‘product’  
and row ‘id’.

I’ve just been discussing this with someone who has the beta Agile 2  
book, and apparently Agile 2 says not to do this, but to use a DBA  
defined other identifier, and to leave the database assigned id  
attribute to the database, and not to use it for foreign keys.

The first method seemed to make sense to me, however using migrations  
i’m finding that mysql 5 is autoincrementing the data each migration.  
So, if i add a single object/row of test data to a table in a migration,  
the first time i perform the migration the row is assigned id=1. If i  
then revert, and then reperform this migration, even though there is  
still only one row of data the second time, the item now has id=2.

Just wandering if anyone knows why this is happening, and if it is  
possible to prevent is so that i can still use the row id as a foreign  
key reference?

thanks

---

<div class="post-metadata">

**Author:** ![xia](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/x/bb73d2/32.png) [@xia](https://www.ruby-forum.com/u/xia)\
**Post date:** [July 21, 2006, 1:41pm UTC](https://www.ruby-forum.com/t/migration-and-mysql-row-id/99237/2 "2006-07-21T13:41:39Z")

</div>

i don’t know where this someone got their information, but i believe  
they are misinformed.

you WANT to use the “id” column in the table as the primary key. you  
WANT to point foreign keys to this primary key. in rails you are not  
required to setup foreign key relationships at the database level as  
rails uses the column names to determine relationships. this is not  
to say that you cannot create those references at the database level,  
just that rails doesn’t use it.

ie

## users

id (primary key)  
name

## things

id (primary key)  
name  
user\_id (foreign key to users.id)

user has\_many things  
thing belongs\_to user

section 14.3 in the 1st edition and section 16.3 in the 2nd edition is  
the part of the book i believe you are referring to. I don’t think  
this section has changed.

you are free to override the primary key, but keep in mind that if you  
do, you are now responsible for providing a unique value to that  
column before saving the record.

as to your migration problem, i have never experienced that behavior.  
when you reverse a migration (ie, down) your table is dropped and the  
auto\_increment information should disappear as well. when you migrate  
back up and insert a new record, it should start at 1 as long as you  
are letting rails/db handle the id/primary key.

---

<div class="post-metadata">

**Author:** ![xia](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/x/bb73d2/32.png) [@xia](https://www.ruby-forum.com/u/xia)\
**Post date:** [July 21, 2006, 1:42pm UTC](https://www.ruby-forum.com/t/migration-and-mysql-row-id/99237/3 "2006-07-21T13:42:02Z")

</div>

> key reference?  
> Because MySQL’s autoincrement field, well, it autoincrements and  
> doesn’t auto-reset. You need to execute the following SQL query:

“ALTER TABLE mytable AUTO\_INCREMENT=1”

Then you can add your test data again.

Best regards

Peter De Berdt

---

<div class="post-metadata">

**Author:** ![xia](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/x/bb73d2/32.png) [@xia](https://www.ruby-forum.com/u/xia)\
**Post date:** [July 24, 2006, 5:24am UTC](https://www.ruby-forum.com/t/migration-and-mysql-row-id/99237/4 "2006-07-24T05:24:03Z")

</div>

Peter De Berdt wrote:

> > key reference?  
> > Because MySQL’s autoincrement field, well, it autoincrements and  
> > doesn’t auto-reset. You need to execute the following SQL query:
> 
> “ALTER TABLE mytable AUTO\_INCREMENT=1”
> 
> Then you can add your test data again.
> 
> Best regards
> 
> Peter De Berdt

Thanks, this worked.