I had a case in my service on those days when it was not possible to delete a record because it was referenced by the log table. Of course, that has generated a call for IT to solve.
But such a case made me think. Although by default I avoid deleting records, generally inactive the record, in which case I need to delete a record. So in this case I need to delete the records in the log.
Or rather, is there a way I can check FK only when I insert or update the table records?
For ease let's assume that the records you cited are Usuários and you have a table to record their activities. This activity table, let's call it logs records, say, the date / time and the user's IP when the user logs in.
If a user is removed from the Usuários table, all entries in the logs table are lost. For, without the user to which it refers, the only information that the same guard is the date / time and IP.
In this case, the ideal would be to remove the logs as well. The same can be done automatically by the database using ON DELETE CASCADE .
Another solution, which in my opinion would be "more correct" would be to inactivate the user. For example, you could use a Boolean field called inativo in the Usuários table. You keep the user data, which allows the activity records for the user to continue to have meaning.
Another idea would be to also add a record of when the user was "inactivated" and, after some time, completely remove it.
Instead of getting totally unregistered from the registry, it would be better for me to create the log tables without FKs, and thus be able to delete the records without to generate foreign key error?
It does not make sense for you to have an orphaned log, ie you delete a user and stick with the IdUsuario in the log table, so that this information reads would be useful since you can not know whose it is? The FK sevem to relate the tables and knows that a certain information has a corresponding link.
Or rather, there would be a way for me to check FK only the moment do I insert or update the table records?
You can even create a table without explicitly declared FK (ie, create the column IdUsuario other than FK) and make the Insert or Update checking if it has the IdUsuario in its log table, but deleting that user will fall into the orphan log issue.
What you can do and delete the LOG table record if you have a FK record and only then delete your record from the corresponding table.
Guilherme, I would do just that.
I do not usually create with FK, because the log function we have here in the company is a function of auditoria . That is, we have a TB_USUARIO table and a TB_USUARIO_AUD , TB_USUARIO_SOLICITACAO and TB_USUARIO_SOLICITACAO_AUD .
And so if you need to delete the user, in rotina de expurgo , for example, we will not lose the data because everything will be in the audit.
This way of working also helps a lot in performance da aplicação , because in the "TBs" 'only active data that the application can manipulate.
