Re: Question on Foreign Key Structure/Design

APseudoUtopia wrote:
> 2. I do not want to get rid of any comments, even if the user is
> deleted (on the application level, I'd display something like
> UnknownUser or UnknownUser#1234). Right now, I just have it ON DELETE
> RESTRICT, but that obviously prevents any users who have commented
> from being deleted. How do the more-experienced database admins
> suggest I do in this case? Should I set a DEFAULT of 0 on the
> comments, then use ON DELETE SET DEFAULT? Then, on the application
> level when 0 is found, it displays UknownUser? Or, should I just
> remove the foreign key completely, and on the application level if the
> JOIN for the username returns empty/NULL, display UknownUser#1234?

The alternative and I have a lot of cases where an audit trail is
needed, and a foreign key on "users" will have a ON DELETE RESTRICT
since no information should be deleted.  In such a case put a "Expiry
flag/Inactive flag" on your user table to allow old records to be
viewed, but records can only be inserted/updated/deleted by current
active users.  In this scenario, your user DELETE re-root your delete
statement to only deactivate the user from logging into the database.

Johan Nel
Pretoria, South Africa.