MySQL Forums
Forum List  »  InnoDB

foriegn key,index,key?????? writing a forum code supplied
Posted by: Steve G
Date: February 15, 2005 06:17AM

hello all, first time poster on here....am just wondering if this looks right to everyone....this is what i've created so far.

i have used webyog/sqlyog to create my db.

am unsure on the use of the "KEY" its to do with the indexing which it says you need to have on your foreign keys.....do i need another "KEY"in table "posts" for username??

also does this look like it all links up in the right way???

if further info is needed to give me an answer i'll be checking back lots and can post more info if needed about how i've worked out the primary keys and justification.

thanks for any help offered.....

yours hopefully....


CREATE TABLE `forums` (
`forum_Title` varchar(100) NOT NULL default '',
`forum_Description` varchar(150) default NULL,
PRIMARY KEY (`forum_Title`)
) TYPE=InnoDB


CREATE TABLE `posts` (
`username` varchar(20) NOT NULL default '',
`topic_Id` mediumint(9) NOT NULL default '0',
`post_Message` tinyblob NOT NULL,
`creation` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`username`,`topic_Id`,`creation`),
KEY `topic_Id` (`topic_Id`),
CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`topic_Id`) REFERENCES `topics` (`topic_Id`),
CONSTRAINT `posts_ibfk_2` FOREIGN KEY (`username`) REFERENCES `topics` (`Username`)
) TYPE=InnoDB

CREATE TABLE `topics` (
`topic_Id` mediumint(9) NOT NULL auto_increment,
`forum_Title` varchar(100) NOT NULL default '',
`topic_Title` varchar(100) NOT NULL default '',
`topic_Started` datetime NOT NULL default '0000-00-00 00:00:00',
`Username` varchar(20) NOT NULL default '',
PRIMARY KEY (`topic_Id`,`forum_Title`),
KEY `Username` (`Username`),
KEY `forum_Title` (`forum_Title`),
CONSTRAINT `topics_ibfk_1` FOREIGN KEY (`forum_Title`) REFERENCES `forums` (`forum_Title`),
CONSTRAINT `topics_ibfk_2` FOREIGN KEY (`Username`) REFERENCES `users` (`username`)
) TYPE=InnoDB COMMENT='InnoDB free: 4096 kB'


CREATE TABLE `users` (
`username` varchar(20) NOT NULL default '',
`password` varchar(8) NOT NULL default '',
`email` varchar(50) NOT NULL default '',
PRIMARY KEY (`username`)
) TYPE=InnoDB COMMENT='InnoDB free: 4096 kB'

Options: ReplyQuote


Subject
Views
Written By
Posted
foriegn key,index,key?????? writing a forum code supplied
5642
February 15, 2005 06:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.