Migration and Mysql row ID

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

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.

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

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.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs