MYSQL help

A place to discuss the implementation and style of computer programs.

Moderators: phlip, Moderators General, Prelates

User avatar
whitewater4562000
Posts: 66
Joined: Sat Aug 23, 2008 4:22 pm UTC
Location: Scotland
Contact:

MYSQL help

Postby whitewater4562000 » Fri Apr 15, 2011 10:26 am UTC

Hi all,

I'm trying to create a blog / comment section for our website and am unsure if this is correct. I currently have 3 tables created.

1st - tblBlog (id, title)
2nd - tblPost (id, blogID (FK), author, ....);
3rd - tblComment(id, postID (FK), commentReply (FK), ...)

Anyway the commentReply field will reference the comment id and i am unsure as to whether the code below does what I think it does.

Code: Select all

CREATE TABLE tblComment (
commentID int(11) AUTO_INCREMENT NOT NULL,
postID int(11),
commentReply int(11),
name varchar(50) NOT NULL DEFAULT '',
email varchar(100) NOT NULL DEFAULT '',
comment text NOT NULL,
commentDate date NOT NULL,
PRIMARY KEY(commentID),
FOREIGN KEY(postID) REFERENCES tblPost(postID),
FOREIGN KEY(commentReply) REFERENCES tblComment(commentReply));


I am sure I could make a few change i.e. have the user info in another table but does this code link the commentReply back to the comment table so that I can handle replies to comments.

NOTE: been along time since I have done any SQL and that was in Oracle so slightly different and I have forgotten most of it.

User avatar
Berengal
Superabacus Mystic of the First Rank
Posts: 2707
Joined: Thu May 24, 2007 5:51 am UTC
Location: Bergen, Norway
Contact:

Re: MYSQL help

Postby Berengal » Fri Apr 15, 2011 1:16 pm UTC

That commentReply FK won't work. Well, it will, but it won't do what you want. What you're saying is that every row must have at least one row with the same commentReply value as it does. This is trivially true, since the row itself has the same value as itself. What you probably want is a "FOREIGN KEY (commentReply) REFERENCES tblComment(commentID)".
It is practically impossible to teach good programming to students who are motivated by money: As potential programmers they are mentally mutilated beyond hope of regeneration.

User avatar
whitewater4562000
Posts: 66
Joined: Sat Aug 23, 2008 4:22 pm UTC
Location: Scotland
Contact:

Re: MYSQL help

Postby whitewater4562000 » Fri Apr 15, 2011 2:20 pm UTC

Dammit, can't even copy my own code right, the original code did have FORIEGN KEY(commentReply) REFERENCES tblComment(commentID). So since that is the code I have it should work no problem.


Return to “Coding”

Who is online

Users browsing this forum: No registered users and 2 guests