[SOLVED] linking table help

[mistertylersmith]

hello,

 

mysql version: 5.0.41-community-nt

 

im trying to create a basic forum system.. the problem is when i try to add the foreign key constraints in the replies table i get a 1005 error...

 

these are the create statements i have

 

#
#********Brothers Table********
#
#DROP TABLE `brothers`;
CREATE TABLE `brothers` (
`bro_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`bro_username` VARCHAR( 20 ) NOT NULL ,
`bro_first_name` VARCHAR( 20 ) NULL ,
`bro_last_name` VARCHAR( 20 ) NULL ,
`bro_password` VARCHAR( 100 ) NULL ,
`bro_email` VARCHAR( 100 ) NULL ,
`bro_year` ENUM( 'Freshman', 'Sophomore', 'Junior', 'Senior', 'Alumnus' ) NULL ,
`bro_quote` TEXT NULL ,
`bro_class_letter` ENUM( 'Alpha', 'Beta', 'Gamma', 'Delta', 'Epsilon', 'Zeta', 'Eta', 'Theta', 'Iota', 'Kappa', 'Lambda', 'Mu', 'Nu', 'Xi', 'Omicron', 'Pi', 'Rho', 'Sigma', 'Tau', 'Upsilon', 'Phi', 'Chi', 'Psi', 'Omega' ) NULL ,
`bro_class_group` INT NULL ,
`bro_dob` DATE NULL ,
`bro_active` ENUM( 'active', 'inactive' ) NULL DEFAULT 'active',
`bro_nickname` VARCHAR( 50 ) NULL ,
UNIQUE (`bro_username`),
UNIQUE (`bro_email`)
) ENGINE = innodb;

 

#
#********Forum Table************
#
#DROP TABLE `forums`;
CREATE TABLE `forums` (
`forum_num` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`forum_name` VARCHAR( 100 ) NOT NULL ,
UNIQUE (`forum_name`)
) ENGINE = innodb;

 

#
#********Forum_Post Table*******
#
#DROP TABLE `forum_posts`;
CREATE TABLE `forum_posts` (
`post_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`post_forum_num` INT NOT NULL ,
`poster_id` INT NOT NULL ,
`post_title` VARCHAR( 500 ) NULL ,
`post_date` DATE NULL ,
`post_time` TIME NULL ,
`post_content` TEXT NULL,
FOREIGN KEY (`post_forum_num`) REFERENCES `forums` (`forum_num`),
FOREIGN KEY (`poster_id`) REFERENCES `brothers` (`bro_id`)
) ENGINE = innodb;

 

#
#********Post_Reply Table*******
#
#DROP TABLE `post_reply`;
CREATE TABLE `post_reply` (
`reply_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`reply_post_num` INT NOT NULL ,
`replier_id` INT NOT NULL ,
`reply_date` DATE NULL ,
`reply_time` TIME NULL ,
`reply_content` TEXT NULL,
FOREIGN KEY (`reply_post_num`) REFERENCES `forum_posts` (`post_num`),
FOREIGN KEY (`replier_id`) REFERENCES `brothers` (`bro_id`)
) ENGINE = innodb;

 

in the last code block, that is what i think the foreign key constraints should look like but they do not work..

 

i think the solution is that i need a linking table for the post_reply table but im not sure.

 

thanks for reading

[pdkv2]

In the create table statement for "post_reply" you are referring  "post_num" colum of table "reply_post_num" which is not exists 

FOREIGN KEY (`reply_post_num`) REFERENCES `forum_posts` (`post_num`),

check with this !

 

 

Sheers!

[mistertylersmith]

omg so dumb, thank you though ~_~