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
Quote Reply
Re: [slg_saravanan] Creating Table with Foreign Key In reply to
When creating your table using GT::SQL::Creator, use:

Code:
$creator->fk('foreign_table_name' => { 'local_field_name' => 'foreign_field_name' };
Code:

for further details, check the GT module documentation for GT::SQL::Creator in your admin panel.

Philip
------------------
Limecat is not pleased.
Quote Reply
Re: [slg_saravanan] Creating Table with Foreign Key In reply to
GT::SQL does not use foreign key support that is built into some of the various databases - GT::SQL itself handles the foreign keys.

Adrian
Quote Reply
Re: [brewt] Creating Table with Foreign Key In reply to
When you say it handles the foreign keys, do you mean automatically? How does it do so? Does that mean the creator->fk function is worthless? Does it only create foreign keys for links tables (not custom tables for new plugins?) Do you plan on integrating the fk method in the future to work better?

Thanks... just trying to learn,

- Jonathan
Quote Reply
Re: [jdgamble] Creating Table with Foreign Key In reply to
Quote:
When you say it handles the foreign keys, do you mean automatically? How does it do so?

When you create a table using Creator,
Code:
$creator->fk(table2 => { foo_fk => "foo" })

it adds the foreign key relations information to the .def file.
Code:
'fk' => {
'demo_Links' => {
'LinkID' => 'ID'
}
},

So, whenever you use
Code:
$DB->table([qw/table1 table2/])

the proper JOIN language is added to the generated SQL, so you can do:

Code:
$db->select()
instead of:
Code:
$db->select({"table1.foo_fk" => \"table2.foo"});

Quote:
Does that mean the creator->fk function is worthless?
Nope. See above.

Quote:
Does it only create foreign keys for links tables (not custom tables for new plugins?)
No, it works for ALL tables created using GT::SQL::Creator (standard and plugins), provided that the fk() function is used when creating the table.

Hope that helps.

Philip
------------------
Limecat is not pleased.
Quote Reply
Re: [fuzzy logic] Creating Table with Foreign Key In reply to
So once a creator->fk object is used (which creates the relation in the def file) the foreign key is set though, right? If this is true, then these tests should hold true:

1. If I delete a record, its foreign key record should delete automatically from the original primary key.
2. I should not be able to add a record to the relationship table that does not have a corresponding primary key to its foreign key.

I'm pretty sure I remember testing these things in the past with failed results.

If links automatically handles this, then the InnoDB db type should not have to be present in the mySQL.?.? Does links ever actually use the SQL command 'references table(column)' into SQL?

Are foreign keys REALLY only supported with Select types, and what does that mean to a person creating a new db?

Thanks,

- Jonathan
Quote Reply
Re: [jdgamble] Creating Table with Foreign Key In reply to
Quote:
1. If I delete a record, its foreign key record should delete automatically from the original primary key.

the foreign keys work with deletions, yes. For example, if you delete a record from Links, then all records in any other table which has a matching foreign key on Links.ID (ie, the Bookmark table) will be deleted as well.

Quote:
2. I should not be able to add a record to the relationship table that does not have a corresponding primary key to its foreign key.

If memory serves, insert() and add() do not check this. It's up to you to make sure the relationship is valid prior to inserting the record.

Philip
------------------
Limecat is not pleased.
Quote Reply
Re: [fuzzy logic] Creating Table with Foreign Key In reply to
Thanks, I think I understand now. Because a record is deleted through the GT::SQL modules, it doesn't really matter how mySQL, or whatever SQL program, effects it. Although not manditory, I think that insert() and add() should include this feature.

- Jonathan