Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Creating Table with Foreign Key

Quote Reply
Creating Table with Foreign Key
 

Hi Folks,

I am new to this forum.

I want to know the exact way to create a new table with foreign key reference.

I have one master table..

CREATE TABLE `lsql_Category` (
`ID` int(10) unsigned NOT NULL auto_increment,
`Name` varchar(255) NOT NULL default '',
`FatherID` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`ID`),
KEY `fthrindex` (`FatherID`),
) TYPE=MyISAM.

This is the data residing into that table.
ID Name FatherID Full_Name2 History 36 By Level/GCSE/History









I have one child table..

create table lsql_CachedQueryResults (ID INT(10) NOT NULL,SearchQuery LONGTEXT NOT NULL,
FatherID INT(10) NOT NULL,TotalEssays INT(11) NOT NULL,
FOREIGN KEY (FatherID) REFERENCES lsql_Category (FatherID),PRIMARY KEY(ID)) Type=MyISAM;

Now when I tend to insert following new row into "lsql_CachedQueryResults" table ,it should not accept.

ID --> 11
SearchQuery --> TEST DATA
FatherID --> 6767
TotalEssays --> 77

It(lsql_CachedQueryResults table) should not accept "FatherID --> 6767".It should accept only "FatherID --> 16".

How can I accomplish this ? What would be the problem ?

Please advise on this.

Thanks in advance..

Warm Regards,
Saravanan
Subject Author Views Date
Thread Creating Table with Foreign Key slg_saravanan 2688 Sep 7, 2006, 12:09 AM
Post Re: [slg_saravanan] Creating Table with Foreign Key
fuzzy logic 2635 Sep 7, 2006, 6:35 PM
Thread Re: [slg_saravanan] Creating Table with Foreign Key
brewt 2621 Sep 7, 2006, 10:32 PM
Thread Re: [brewt] Creating Table with Foreign Key
jdgamble 2575 Sep 8, 2006, 2:13 PM
Thread Re: [jdgamble] Creating Table with Foreign Key
fuzzy logic 2571 Sep 8, 2006, 3:48 PM
Thread Re: [fuzzy logic] Creating Table with Foreign Key
jdgamble 2575 Sep 8, 2006, 8:18 PM
Thread Re: [jdgamble] Creating Table with Foreign Key
fuzzy logic 2549 Sep 8, 2006, 8:56 PM
Post Re: [fuzzy logic] Creating Table with Foreign Key
jdgamble 2569 Sep 8, 2006, 9:08 PM