Log tables should have Foreign Keys?

Asked

Viewed 280 times

7

I had a case in my service these days when it was not possible to delete a record because it was referenced by the table of log. Logically what generated a call for the IT area to solve.

But that case got me thinking. Although by default I avoid deleting records, usually inactive the record, in which case I need to delete a record. So in this case I need to delete the logs.

Instead of me being totally without log log, it would be better if I create the log tables without Fks, and thus be able to delete the logs without generating error of foreign key?

Or rather, there would be a way for me to check FK only at the time I enter or update the table records?

sql sql-server
  • 1

    The doubt is interesting, but the answer is probably "only depends on each case". I’ve seen great systems that don’t use FK for absolutely nothing, because the application knows how to handle the data. On the other hand, log if it’s just log, it shouldn’t be tied to anything. Probably you are talking about these cases that for lack of using a temporal DB architecture, everything that is edited is logged into a separate table (I find it poor, but often it is what is done).

  • 1

    The question I would ask for DBA is another: why use FK on such a table instead of just storing the ID without forcing a relationship? You might as well keep the original ID and not do it as FK.

  • @Bacco not making her a FK doesn’t make sense. Because if a record is removed, all entries in the log table that referred to it will have an ID that has no meaning. And, depending on how the Ids of the records are generated, there is a risk of a new record assuming an ID of another already deleted record.

  • 1

    @Gabrielcandez doesn’t make sense in the specific scenario you imagined, in others can make sense. As I said in the first comment, it depends on each case. If it can delete the user, there may be no problem with the orphaned ID. It may well be that the log information is important to check things that do not depend on the name of the person who did it. Only the real case can tell what makes sense or not.

3 answers

5

For ease let’s assume that the records you quoted are Usuários and you have a table to record their activities. This table of activities, let’s call it logs records, say, the user’s date/time and IP when logged in.

If a user is removed from the table Usuários, all entries in the table logs they lose their meaning. Because, without the user to whom it refers, the only information that the same guard is the date/time and the 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 it could use a boolean field called inativo on the table Usuários. You keep user data, which allows activity records relating to the user to continue to make sense.

Another idea would also be to add a record of when the user was "inactivated" and after a while remove it completely.

2

Instead of me being totally without log logging, it would be better if I create the log tables without Fks, and thus be able to delete the records without generating Foreign key error?

William, I would do just that.

I do not usually create with FK, because the function of the log that we have here in the company is a function of auditoria. I mean, we have a table TB_USUARIO and a TB_USUARIO_AUD, TB_USUARIO_SOLICITACAO and TB_USUARIO_SOLICITACAO_AUD.

And then if it is necessary to delete the user, in a 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 "Tbs"`there will only be active data that the application can manipulate.

2

Instead of me being totally out of log log, I’d better create log tables without Fks, so you can delete the logs without generate Foreign key error?

It doesn’t make sense that you have an orphaned log, that is, you delete a user and stay with Idusuario in the log table, so that this information reads would be useful since you can’t know whose it is? The FK sevem to relate the tables and know that a certain information has a corresponding link.

Or rather, there would be a way for me to check FK only at the moment insert or update the table records?

You can even create a table without explicit declared FK (i.e., create the Idusuario column without being FK) and do Insert or Update by checking if you have Idusuario in your log table, but if you delete this user will fall into the orphaned log issue.

What you can do and delete the record from the LOG table if you have a FK record and only then delete your record from the corresponding table.

Browser other questions tagged sql sql-server

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