Question :
A strange thing happened to my company’s Oracle database:
Foreign key constraints keep dropping out by themselves.
If you could solve this problem, that would be great. But even if you can’t, another method would be great, that is,
Setting up an alert that will send an email when a foreign key constraint is dropped.
If you could solve any of the above 2 problems, it would be great to me.
Answer :
Foreign keys kept dropping out by themselves.
Unless there is a major bug (and that would be talked about a lot online) in the version you are running, then this should not happen. Something is asking for the keys to be dropped rather than the engine doing it automatically.
Setting up an alert that will send an email when a foreign key is dropped.
You could use a DDL trigger (search the documentation for “DDL Trigger”, on online more generally for examples) to log the exact time that a DROP statement takes action. I don’t know if sending mail is possible/practical in Orcale but you can definitely log the events to a table that you can refer to later.
The trigger may give you access to the user account that is used, though this may not be useful if it is being caused by an application user rather than a more specific login (i.e. a human admin). Knowing the exact time will allow you to more easily work out what process (an errant index maintenance script, someone deploying updates that are incorrect and reset the table’s keys to an older incorrect setup, and so on) is asking for the keys to be dropped.