Re: Question on Foreign Key Structure/Design

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