Table-agnostic Foreign Keys?

Question

First, I did read this StackOverflow question, so no need to point me towards it.

I'm working on a similar problem right now. Specifically, I have a database with an Auditing table that is used to store auditing info about other tables within the db. The basic form of this table is:

ID, EntityID, EntityTypeID, ActionTypeID, DateTime

Now, as you can guess, the generic nature of EntityID means that foreign key relationships to and from this table are tricky to manage, especially with you through an ORM system into the mix.

Of course, the grunt-work solution is to do the required queries manually and use the ORM stuff where it works, which I'm fine with.

However, the issue did raise the question in my mind as to whether or not there exists any RDBMS out there that allows for a Foreign Key relationship of the form: Table:ID to be defined.

In other words, in such an RDBMS, the EntityTypeID column might contains values such as

'TableA:1' and 'TableB:somekey'

So...

Is there any RDBMS which does this?

Answer 1

Audit tables usually cannot have referential integrity constraints on them. An audit table, A, records information about a row of data, R, in some table, T, and contains a series of records for R, each representing R at some a different time. When R is subsequently modified, the information in A does not change, and is not allowed to prevent changes to R. When R is subsequently deleted, the presence of the audit records in A is not allowed to stop that deletion.