0

I have the following tables:

CUSTOMER: CustomerID, CustomerName

PROJECTS: ProjectID, ProjectName, CustomerID (referencing CUSTOMER.CustomerID)

Currently, customer ID's are from 1 to 999. I need to change thes existing records so that all customer ID's start with 5. In other words, add 5000 to CustomerID in all rows. Of course that won't work since the table is "connected" (I don't remember the correct word).

Are there any correct way of doing this? Built in functions?

Or do I manually have to delete the foreign keys, then update all rows manually in all referenced tables, then recreate the foreign keys?

Edit: I know changing ID's is not good practice etc, but let's just accept that it must be done in this case.

Edit2: I don't think it's duplicate to referenced question. They are asking about a stored procedure? And also accepted solution is apparently a bad solution?

James T
  • 99
  • 6
  • You need `ON UPDATE CASCADE` feature of foreign keys – Madhur Bhaiya Jul 02 '19 at 14:54
  • Possible duplicate of [How to update primary key](https://stackoverflow.com/questions/2499246/how-to-update-primary-key) – isaace Jul 02 '19 at 14:57
  • @MadhurBhaiya I haven't heard of Cascade before - but it sounds like if I add that to my table, if I update the IDs, they will be automatically be updated in all referenced tables, exactly what I'm asking about? (and I can just remove UPDATE ON CASCADE after i'm done). Is this correct? Almost sounds too good to be true! – James T Jul 02 '19 at 15:06
  • Give it a try..read up on it here: https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html#foreign-keys-referential-actions – Madhur Bhaiya Jul 02 '19 at 15:08
  • As you've observed, the correct way is to not do this. Consequently there is no correct way. – Strawberry Jul 02 '19 at 15:10
  • @MadhurBhaiya : It seemed to work exactly like I wanted! Thanks! You should post it as answer – James T Jul 02 '19 at 15:47

0 Answers0