24
rails console
u = User.find(9)
u.id = 7 # There is no other record with id 7
u.save
=> true
User.all

The id has not changed.

How to change the primary ID? Why does it prevent this action?

Working in Rails 3.0.7 and PostgreSQL.

EDIT: Since there are good reasons not to do this, I'll explain why and hopefully it is a good reason.

We are doing Usability Testing on Staging, so I want it to look like the Production to make it easy for everyone. I don't want to create confusion or errors in the Usability Testing process by having some things in one order on Staging and in a different order on Production. I only changed one PK id on Staging DB.

Don't do this on your production DB!

B Seven
  • 44,484
  • 66
  • 240
  • 385
  • 2
    possible duplicate of [Overriding id on create in ActiveRecord](http://stackoverflow.com/questions/431617/overriding-id-on-create-in-activerecord) – Rick Smith Sep 09 '15 at 22:42

6 Answers6

34

I'm not sure as to why it prevents that action, but it does for sure prevent it.

You can bypass this using the update_all method on for the user. 

User.where(id: 7).update_all(id: 9)

Though if possible, you really should avoid doing this.

Kyle d'Oliveira
  • 6,382
  • 1
  • 27
  • 33
15

For me worked:

User.find(9).update_column(:id, 7)
Magne
  • 16,401
  • 10
  • 68
  • 88
tomaszbak
  • 8,247
  • 4
  • 44
  • 37
  • This depends on the DB or DB-adapter used. I got: `ActiveRecord::StatementInvalid: TinyTds::Error: Cannot update identity column 'id'` – Magne Apr 03 '18 at 12:14
8

Could you elaborate what your use case is? Why do you want to change the ID? What are you really trying to accomplish with it?

Generally it's a bad idea to do this, and Rails won't let you do this easily because it will break your data integrity!

Here's Why: When you're using a relational database (like PostgreSQL) underneath, you will have relationships between your models, which means that you will use the model's IDs as a reference in other related models... which means that if you change an entry's ID , all those references to that entry will go stale and corrupt your database..

I would strongly suggest to analyze your requirements again, and try to find another way to accomplish what you need to do (without changing IDs)

Tilo
  • 33,354
  • 5
  • 79
  • 106
  • 2
    Thanks for the warning. I am making my Staging DB look like the Production DB. I understand that all the records that reference User ID 9 will be orphaned. – B Seven Nov 28 '11 at 20:27
  • how about you just load a DB-dump from your production DB? wouldn't that work? That would make sure that the state of all tables is valid. – Tilo Nov 28 '11 at 20:29
  • I don't want all the data from production. I just want the users in the same order. Don't worry, there are only 8 of them and there was only one reference to #9 which I fixed. – B Seven Nov 28 '11 at 20:32
  • oh, ok. I haven't used PostgreSQL , but in MySQL you can dump tables separately -- this way you could accomplish the same thing with a partial DB-dump (without having to tweak IDs manually) – Tilo Nov 28 '11 at 20:35
  • There was only one ID to tweak, so it wasn't a big deal. – B Seven Nov 28 '11 at 20:46
  • Still, as AR should know a good deal about the relations, it could awesomely update all the references. Of course that is a super-risky operation and I agree that users should not be encouraged to do so. – Felix Nov 23 '17 at 10:21
5

Another method (although it is not pure Rails) is to create a new column, and populate it with your new IDs.

Then, using DB management software (not Rails), remove the Primary Key attribute from the id column, delete it, rename your recently added column to "id", and give it the Primary Key attributes. (If you cannot do that directly in your DB software, then set the properties Unique, Auto-Increment, etc.)

You can also move the column to the front (MySQL syntax):

ALTER TABLE table_name MODIFY COLUMN id int(11) FIRST;

But there is another thing I'd really like to say. It hasn't been as bad on this question as I've seen elsewhere, but folks: it's all well and good to tell people it's USUALLY not a good idea, but that isn't an answer to the question. Please refrain from saying "Don't do that" unless you already know the person's use-case.

In other forums I've been greatly frustrated by people saying "Why do you want to do that?" or "Don't do that", and then not answering the question. They didn't give me credit for already KNOWING that it isn't standard practice, and they ASSUMED I didn't already know that it was not an ordinary use-case.

People on this page haven't been that bad, and I'm not trying to pick on them. I'm just admonishing: please, check your own behavior before presuming to lecture. Somebody asked a question, and while warnings may be a good idea, it is probably safe to presume they have a REASON for wanting an answer.

End of rant.

Lonny Eachus
  • 366
  • 3
  • 7
5

@Jason pointed out a very valid point. I totally agree with him. But, you might have your reasons and I suggest you re-consider what you're trying to do.

To answer your question:

ID columns are protected by default for mass assignment and cannot be set manually. But, you can override this behavior by defining a method:

def self.attributes_protected_by_default
  [] # ["id", ..other]
end

This will allow you to assign id's manually.

Syed Aslam
  • 8,707
  • 5
  • 40
  • 54
-1

The ID is typically generated by the database as an auto-incrementing PK. You can't (and really shouldn't need) to modify it.

Jason
  • 86,222
  • 15
  • 131
  • 146
  • Oh well, at least there is a good reason...it turns out there is a way. See Olives' answer. – B Seven Nov 28 '11 at 20:08
  • 1
    Having worked in older db's that did allow you to do this, I can tell you from experience that it's a horrible idea. Allowing this makes it extremely trivial to destroy the referential integrity of your db. – Jason Nov 28 '11 at 20:09
  • 1
    Yes, I know. I am setting up my staging DB to be like production DB. – B Seven Nov 28 '11 at 20:12