3

I am having problem with how the primary key gets incremented when I delete_all from a table and start inserting again. This is problematic because I have other models that are associated with the purged table. For example, if I have Apple that belongs_to Computer, then when I purge the Computer table and re-add the computer, the associations get messed up because Apple now has infant computer_id in its rows. I want to be able to reset the primary key of the computer so that when I re-add the computer, the association still stays intact. How should I go about doing this in rails?

Edit I am very sorry for not being clear about this, but now I will tell you want I want to do. I want a user to be associated with a machine (computer). A computer has his own id and can only be created by the admin. There are a fixed amount of computer in the computer table.

User
belongs_to :computer

Computer
has_many :users

The problem that I am facing is that, I have the computer table pre-loaded inside seeds.rb/some rakefile. When I start up my app, I want to just run the file that first deletes everything and then pre-loads the table. This will let the user choose a computer only from the ones provided in the computer table. I want to be able to run this script again (say in heroku console, without dropping the User table) and still get the same id associated to each machine (and thus to each user). I am confused on how I should do this. Please let me know if I should do this differently.

denniss
  • 17,229
  • 26
  • 92
  • 141
  • You shouldn't load this kind of data with seeds or fixtures or whatever. It should be maintained through some admin interface. Don't use two primary keys - you will end up in big mess :) – klew Dec 28 '10 at 08:15
  • Maybe try to explain what are you doing and why you want to do it. – klew Dec 28 '10 at 08:16

3 Answers3

12

First, you shouldn't do it! If you want to change record, then just update rows in your table. Don't delete it and reinsert it! If you still want to do it, then you should delete all associated rows in other tables and insert both computers and apples.

I don't know Rails way of reseting auto increment primary key, but if you use mysql then you can run custom query:

ActiveRecord::Base.connection.execute('ALTER TABLE tablename AUTO_INCREMENT = 1')

it should reset auto increment value to next available number.

EDIT:

How to load data with id. I'm not sure if loading with seeds can do it, but for sure using fixtures can help. What you need is to prepare computers.yml file with data:

First_computer:
  id: 1
  name: computer

Second_computer:
  id: 2
  name: dell

etc.

Then you can use this to load it to db:

require 'active_record/fixtures'

Fixtures.create_fixtures("/path/to/directory/where/your/yml/file/is/", "computers")

But it will reset whole table (all rows will be deleted and reinserted - but you can keep your ids).

Again I want to warn you that it is bad idea to load data this way. Fixtures/seeds should be used only to seed your db with initial (needed to start your application) data.

Why do you want to reset this table?

klew
  • 14,837
  • 7
  • 47
  • 59
  • Are you suggesting that I should just add new computer without delet_all the table? The problem that I have is that when I destroy_all the tabel and start adding new computers, the users' will have infant computers. How should I preload the data then so that the id and stays the same everytime I run the script to load the data. – denniss Dec 28 '10 at 10:08
  • Loading data with fixtures resets table. It also resets ids. – klew Dec 28 '10 at 10:12
  • 1
    You shouldn't relay other model relations on this. It is bad idea :). I still don't understand why you want to reset this table? Why not just update each field without deleteing it? Or if it is some kind of const data in your application, you can try using this: https://github.com/jeffp/enumerated_attribute – klew Dec 28 '10 at 10:18
  • I used this to get it working. Importing data into the db without resetting the id counter can get you in trouble with new records trying to use already existing ids. Answer was here: http://stackoverflow.com/a/23998870/293856 – unom Feb 10 '17 at 19:21
2

That's an odd way of doing things, but if you need to remove and replace the keys in the associated table, you could try specifying :dependent => :nullify, which preserves the associated rows and sets the foreign key to NULL when deleting:

class Computer
  has_many :apples, :dependent => :nullify
end

class Apple
  belongs_to :computer
end

c = Computer.find(some_id)
apples = c.apples
c.destroy # sets each foreign key in apples to NULL
new_c = Computer.create
new_c.apples = apples # sets each foreign key to new_c.id
zetetic
  • 47,184
  • 10
  • 111
  • 119
1

You should not rely on resetting primary keys for maintaining relationships, that's not the right way to do it. If you find yourself having to delete the source table while still maintaining the relationship in the future, use a different key for mapping, for example you could have a code field in the computer table and then use that for constructing your relationships. You specify the foreign_key for your relationships.

Oscar Del Ben
  • 4,485
  • 1
  • 27
  • 41
  • I thought so too... I am trying to use the additional options that rails provide. so if I want computer to have a primary key called machine_id (which will be used as the foreign key in Apple), do I do this? has_many :apples, class => 'apple', :foreign_key => 'machine_id' – denniss Dec 28 '10 at 08:03
  • @denniss: it is really good idea to follow "Rails way", so always use `id` as a primary key - it is much, much simpler. And I'm not sure if your example association in right. It would be right if you had `machine_id` column in `Apple` model/table. If you could add both models to your question and tables schema, then we could help more. – klew Dec 28 '10 at 08:11