Thread: Question on Foreign Key Structure/Design

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.

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?

Thanks for any and all advice on the subject. Your time is appreciated.

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.

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

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.