How to update primary key and it's references to other tables at the same time

Question

I have a main table with primary key as 'main_id'. This 'main_id' is being used a foreign key in two tables : 'sub_table1', 'sub_table2'

I have a requirement where I need to change the 'main_id' and it's references in 'sub_table1' and 'sub_table2'.

I am on Nodejs and using Bookshelf and Knex.

So far I tried updating the sub_tables first and then tried updating the main_table. But it threw error 'Foreign_key constraint being violated'.

I'm new at using Bookshelf. Please help

Answer 1

Foreign keys are absolutely constant and this might not be what you actually want to do. I mean, there is no real reason why you would like to change the id as it's an identification for you as a developer to work with, not something that should be able to be overwritten. If for some reason you need the user to have something similar to an identification that they can overwrite, you can simply create a new column called for example 'code' or 'customId' (or whatever, you got the point) and make it unique.