Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

Help with foreign key relations

Quote Reply
Help with foreign key relations
Hi, I know you have used foreign keys Ivan, and I am wondering if you or anyone else could straighten its use out for me.

This is a ficticious example (the actual fields are not real for this example), but lets say I wanted this new database which is created during plug-in install, to always mirror my users file (i.e. same number of records, idential ID's for each user). Assuming when you add or delete a record from the users table, this table would automatically be affect also. Is this syntax correct? Would someone mind confirming that the action is oneway i.e. only changes to the master table (Users in this case) will affect the other table, and not the other way around???Crazy

Code:
$c2->cols (
ID => { pos => 1, type => 'INT', not_null => 1, regex => '^\d+$' },
Announcements => { pos => 2, type => 'CHAR', size => '255', not_null => 0, default=> 'None.' },
blah blah
Username => { pos => 7, blah blah blah }
);
$c2->pk('ID'); <<Do I still need to specify the primary key when using an fk?
$c2->ai('ID');
$c2->fk ( {
'Users' =>
{ ID => ID,
...
LinkOwner => LinksOwner
}
}
);

if (! $c2->create()) {
$GT::SQL::errcode ||= '';
$GT::SQL::errcode eq blah blah blah
$c2->set_defaults();
$c2->save_schema();
};




I apologise for the untidy formatting, cut and paste is behaving strangelyBlush


http://www.iuni.com/...tware/web/index.html
Links Plugins

Last edited by:

Ian: Jun 29, 2002, 1:32 PM
Quote Reply
Re: [Ian] Help with foreign key relations In reply to
Alex may have to address this, I think in an old email he had indicated this system was only rudimentary, and might not be maintained.

The documentation says fk relationships are enforced for selects only.

Beyond that, you have to maintain the foreign key relationship yourself ie: carry your changes manually (step by step) through each of the tables you affect.

I had initially been excited by foreign keys, but lost some enthusiasm at that point. It became easier to carry the logic through internal to the code, than to allow the "database" to try to do the housekeeping.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] Help with foreign key relations In reply to
Thanks for the comments. Maybe Alex could clarify these points for me?

I did notice the comment on the docs, about pk being available for selects only also. Hmmmm.


http://www.iuni.com/...tware/web/index.html
Links Plugins
Quote Reply
Re: [Ian] Help with foreign key relations In reply to
Hi,

What is the relationship you are trying to setup?

Foreign Keys are very useful in GT::SQL. They accomplish two main tasks:

1. In selects, your tables are auto joined on the foreign key: i.e. For Links and CatLinks, where CatLinks.LinkID is a foregin key to Links.ID, when you do:

my $rel = $DB->table('Links', 'CatLinks')->select;

that turns into:

SELECT * FROM Links,CatLinks WHERE Links.ID = CatLinks.LinkID

automatically.

2. When you delete a record, the delets automatically cascade. So if you delete a link with ID 3, it will automatically `DELETE FROM CatLinks WHERE LinkID = 3`.

Hope that helps,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] Help with foreign key relations In reply to
Hi Alex,

Thanks for the reply.

Quote:


What is the relationship you are trying to setup?


In this particular case, I was trying to create a relationship between Links and a custom table, which would share the same link ID.

The idea was the links table would be the master, and my custom table always mirror the same Link ID's in the links table.

It seems from what you are saying this is possible. I am wondering if the format of my syntax (in red) in the top post of this thread is in fact correct?

I have a related (excuse the pun) question which I posted in the discussion forum this evening. Can a relationship be made between any two or more tables with Links::SQL? Example ('Links','Reviews')?


http://www.iuni.com/...tware/web/index.html
Links Plugins

Last edited by:

Ian: Jun 30, 2002, 11:31 PM
Quote Reply
Re: [Alex] Help with foreign key relations In reply to
This sort of needs to be sketched out. I tried looking at the .def files, and they seem pretty complicated.

The LinkID and Username are two primary keys.

If LinkID is deleted from the Links table, the CatLinks with LinkID=deleted_ID are deleted. What about other tables where LinkID is used?

If Username is deleted from the Users table, then are all LinkID's with that LinkOwner also deleted? If so, then the cascade would need to go through the CatLinks and other related tables...


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] Help with foreign key relations In reply to
Hmmmm....good questions pugdog... I would also like to this explained/re-explained myself.


http://www.iuni.com/...tware/web/index.html
Links Plugins