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.