Subject Check constraint vs. ref. integrity
Author Bob Murdoch
I'm setting up a new database, and wondering what the consensus is on this

I have a couple of audit columns that are part of every table in the
database. Those columns are simply Ins_User_ID, Ins_TS, Upd_User_ID and
Upd_TS, representing the user and timestamp of the insert, and the most
recent user and timestamp of an update.

I could create a foreign key from each table to the Users table, linking
the Ins_User_ID and Upd_User_ID to the primary key of the Users
table. Alternatively, I could add a check constraint (or manually create
BeforeUpdate/Insert triggers) to do the validation.

Is there a consensus on which way is better? I know that I should not
create foreign keys to tables that serve as solely as lookup values due to
index non-selectivity, such as a table holding a few status codes. It
would seem to be the same case here, but just checking.


Bob M..