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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
