foreign key problems

[steve g]

hello all this is my first post and am in a bit of trouble with creating my relations……have been trying to do this for a few days now and am a bit behind schedule and getting stressed…. 😡 ….but hopefully one of you kind ppl might be able to turn it upside down???

this is a batch export so that i can show the problems……i have been trying to link up my tables and last night i got one link from “USERS” to “POST”…i now need to link the others up…

i have rearranged the colums.

i have added indexes in the gui etc…

when i'm trying to make the links its coming up with a dialog box saying it can be 1 of 4 errors

**improper relationship definition

**wrong table type and a couple of other that i can't remember at the min…

any help would be greatly appreciated as dunno what i'm doing wrong….thanks

/*

SQLyog v4.02

Host – 84.92.111.23 : Database – glitchDb

**************************************************************

Server version 4.0.23_Debian-1-log

*/

create database if not exists `glitchDb`;

use `glitchDb`;

/*

Table structure for forums

*/

drop table if exists `forums`;

CREATE TABLE `forums` (

`forum_Title` varchar(100) NOT NULL default '',

`forum_Description` varchar(150) default NULL,

PRIMARY KEY (`forum_Title`)

) TYPE=InnoDB COMMENT='InnoDB free: 4096 kB';

/*

Table structure for posts

*/

drop table if exists `posts`;

CREATE TABLE `posts` (

`username` varchar(20) NOT NULL default '',

`topic_Id` mediumint(9) NOT NULL default '0',

`post_Message` tinyblob NOT NULL,

`create_Date` time NOT NULL default '00:00:00',

`create_Time` date NOT NULL default '0000-00-00',

PRIMARY KEY (`username`,`topic_Id`,`create_Date`,`create_Time`),

CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`username`) REFERENCES `users` (`username`)

) TYPE=InnoDB COMMENT='InnoDB free: 4096 kB';

/*

Table structure for topics

*/

drop table if exists `topics`;

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`)

) TYPE=InnoDB;

/*

Table structure for users

*/

drop table if exists `users`;

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';

[steve g]

and what is ibfk 1, as i might be able to edit it myself…….was gonna use postgres instead but need to use dreamweaver too which doesn't support it although i think there's a plug in….but just want to get these tables linked so i can actuall code up the actual forum in php etc..

this is all new to me and have never tried to write anything like this before…so sorry if i sound stupid or i'm making blatent errors??

😉

[CalEvans]

Using your script and moving the users table to the top of the list, I was able to re-create your structure without a problem.

If you have data in the tables already, could it be failing the new constraints? If you have to create the tables out of order, use

set foreign_key_checks=0;

first. That keeps mysql from freaking out until after you've finished.

HTH,

=C=

[steve g]

there isn't any data in the tables am just trying to create from scratch…..it well let me create what i have outputted in the batch script but it only has the one foreign key relationship… i need to hook up the other ones too which is when i hit the problems……

i have just tried to re create the data base again in the gui and it lists the tables alphabetically so i can't have users at the top at it auto goes to the bottom….

this is the third time i've recreated to try and get round things…..and to be honest i don't think the relationships are wrong…so dunno why it won't let me create any others…

thanks for any help that's offered

[steve g]

right…..have recreated the database again and this is what i've cut and pasted out of the gui result window…

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'

this is what i now have, and have been reading over indexing and relationships and am still a bit confused about it all…..the keys, the foreign keys and the actual indexing of them.

does this look right and make sense???

thanks

🙂

[Ritesh]

Yes.

The key statements are:

Code:

CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`topic_Id`) REFERENCES `topics` (`topic_Id`),
CONSTRAINT `posts_ibfk_2` FOREIGN KEY (`username`) REFERENCES `topics` (`Username`)

and

Code:

CONSTRAINT `topics_ibfk_1` FOREIGN KEY (`forum_Title`) REFERENCES `forums` (`forum_Title`),
CONSTRAINT `topics_ibfk_2` FOREIGN KEY (`Username`) REFERENCES `users` (`username`)

I hope they solve your purpose.

[steve g]

yeh but the othe bits that are just “KEY”'s bit i have a KEY for topic_id iin the posts table(which is foreign yet not usrename which is also foreign.

and “KEY”'s in topics which is username and forum title which are both foreign….so do i also need to make another key for the posts table with the username????

wasn't sure on the using of the indexing but it said you have to index feriegn keys in this program in the help so this is what i have now…….just wondering if i still need to add more….so i know whether to move and and start doing the rest as will need to link it to dreaweaver now and start using it.

thanks for your input. 🙂

[steve g]

have re-done it all again…..was getting confused on the moving of foeign keys into becoming partial primary keys….didn't really want to use autonums but have changed it to that now and think its a lot more simplified and will hopefully work when i add the data….fingers crossed

Users CREATE TABLE `Users` (

`username` varchar(20) NOT NULL default '',

`email` varchar(50) NOT NULL default '',

`password` varchar(8) NOT NULL default '',

PRIMARY KEY (`username`)

) TYPE=InnoDB

Forums CREATE TABLE `Forums` (

`forum_Title` varchar(100) NOT NULL default '',

`forum_description` varchar(150) default NULL,

PRIMARY KEY (`forum_Title`)

) TYPE=InnoDB

Topics CREATE TABLE `Topics` (

`topic_Id` mediumint(9) NOT NULL auto_increment,

`username` varchar(20) NOT NULL default '',

`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',

PRIMARY KEY (`topic_Id`),

KEY `username` (`username`),

KEY `forum_Title` (`forum_Title`),

CONSTRAINT `Topics_ibfk_2` FOREIGN KEY (`forum_Title`) REFERENCES forums“forum_Title`),

CONSTRAINT `Topics_ibfk_1` FOREIGN KEY (`username`) REFERENCES `Users` (`username`)

) TYPE=InnoDB

posts CREATE TABLE posts (

`post_Id` int(11) NOT NULL auto_increment,

`topic_Id` mediumint(9) NOT NULL default '0',

`post_Message` tinyblob NOT NULL,

`creation` datetime NOT NULL default '0000-00-00 00:00:00',

`username` varchar(20) NOT NULL default '',

PRIMARY KEY (`post_Id`),

KEY `username` (`username`),

KEY `topic_Id` (`topic_Id`),

CONSTRAINT `Posts_ibfk_2` FOREIGN KEY (`username`) REFERENCES `Users` (`username`),

CONSTRAINT `Posts_ibfk_1` FOREIGN KEY (`topic_Id`) REFERENCES `Topics` (`topic_Id`)

) TYPE=InnoDB

[Author]

Posts