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