APseudoUtopia wrote: > 1. Do I need "NOT NULL" in the comments(userid) column? Yes, unless you want it to be possible for a comment to have a NULL `userid' field. Foreign key REFERENCES state that _if_ there is a value in the referencing field, it must exist in the referenced key. The foreign key reference does not imply that the referencing column may not be null. There are uses for nullable foreign key references, so it's a good thing that a foreign key references doesn't imply non-null. > 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? I'd make it nullable and use ON DELETE SET NULL. In this case you DO want it to be possible to have a comment with no/unknown user, after all. The alternative is a bit of an ugly hack - creating a special user with ID zero, "no user", for the foreign key reference. Ick. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
