Re: Question on Foreign Key Structure/Design

APseudoUtopia wrote:

> Hey list,
>
> I have a table with user IDs, among other information. I also have a
> table of comments that users can place on a page.
>
> CREATE TABLE "users" (
> "id" SERIAL PRIMARY KEY,
> .......
> );
>
> CREATE TABLE "comments" (
> "id" SERIAL PRIMARY KEY,
> "userid" INTEGER REFERENCES "users" ("id") ON DELETE RESTRICT,
> .........
> );
>
> I'm new to the use of foreign keys and this is the first design I've
> created with them being implemented. I have a couple questions on the
> setup.
>
> 1. Do I need "NOT NULL" in the comments(userid) column? users(id) is
> automatically NOT NULL due to the primary key, but I'm not sure if
> comments(userid) needs NOT NULL as well, or if the foreign key will
> automatically transfer that property over.
>

If comments must always be associated with a user, you should add the
NOT NULL, to enforce that. You probably also want an index on that column.

> 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?
>

You can't have an ID of 0 with a foreign key in place, unless there is
a user with that ID. But rather than use 0, you should use NULL for
that (NULL means "unknown" whereas 0 should always have meaning). The
foreign key will still accept NULL. When you add the foreign key, use
the "ON DELETE SET NULL" clause for this purpose (instead of RESTRICT).
Otherwise, the table structure you list above looks fine.