Remove a Foreign key from a production server?

Asked

Viewed 108 times

2

I have a table cad_address with the registration of the address of my users. I also have another order table mov_order that has a key Foreign address_id to the address table, responsible for the relationship.

The problem is that when the guy updates the address, he also updates the order address (because of the relationship). Because of this, we are creating a "snapshot" of the address in the order table, with all the address data, now also in the order (a replica).

Now in the order status we will no longer search the customer’s address by relationship, and yes, directly in the order table, having then the address that was registered at the time of purchase. We take advantage of Foreign key address_id, to migrate customer address data to the order table.

However, we feel now that the order table has become a little dirty. The fact is, we have a column address_id, setting up a relationship that’s not being used for anything. And this relationship can confuse developers, not to mention, that can open a gap for someone to do a little 'shit', call a relationship that is no longer being used...

The question is: Is it right to remove this column? Or should it be maintained?

mysql sql migration

  • Is the problem after all with Laravel? If it’s not, I’m going to take the tags off, to make it easier to relate the issues. It seems to me to be more a simple database issue, I think even PHP should not be as tag.

  • The Laravel is related to the fact that I am using the Laravel Migrations system to make the migrations, and their resources are somewhat limited. That’s why I attacked the tags

  • The system uses Migrations, but the question says nothing about Laravel’s Migration. You use tags only to highlight the subject of the question, not what you use in development. I also confuse this point.

  • Got it! Thanks for the guidance, and sorry for anything.

  • It’s all right, I confuse that too.

1 answer

0


If you already have a replica of the data, remove the column address_id, as you said yourself, it adds nothing but can cause confusion with programmers.

Also, you already have the order table related to customer table which in turn is related to addresses.

Note: It is important to warn customers that the delivery address cannot be changed after purchase.

  • Oops! First of all, thanks for the expensive answer, it really helped a lot! But there is a problem that I forgot to mention up there, I have all this ready to run in the 'Migrations' of the Aravel. It will add the new fields and migrate the data to these new fields. I have tested and it works, everything perfectly. The problem is that I have to develop tbm her rollback, which would be the alternative if something goes wrong, and the problem with this case is that if I remove this column while running Migration, if I need to run the rollback, I will no longer have the values of Foreign key set

  • Because Laravel’s rollback only takes care of the structure and not the data, you would have to back up the table, and if you need a rollback, you would have to restore it under another name (example: mov_order_old), and then recreate the address_id column in the mov_order table and then update the values from the existing ones in mov_order_old.

  • But when/how would I remove this backup table? I was thinking here, it really makes sense to create a backup table to run the Migration and on the rollback update the order table using the backup table... However, would I have to view this table tbm? Why and if so, something happens a few days after the deploy and I need to rollback...

  • Yes, to be complete and with rollback possibility, would have to Versionar the table yes.

Browser other questions tagged mysql sql migration

You are not signed in. Login or sign up in order to post.