[Dbix-class] multi-column primary foreign key

John Napiorkowski jjn1056 at yahoo.com
Tue Jan 16 15:09:00 GMT 2007


--- Joss Shaw <jossblowing at yahoo.co.uk> wrote:

> Hi all,
> 
> I'm just a little confused by all the various
> documentation. I'm attempting to create a table
> schema which has a multi-column primary foreign key.
> That is, the primary keys of table A, are contained
> in table B and table C.  I'm not quite sure how to
> go about this, I tried to use "has_one" and
> "might_have" but apparently that relationship can
> only be used to infer a join for a single primary
> key.
> 
> 
> Could anyone help me please ?
> > joss

Hi,

Not sure if this is the help you need, but I use
multicolumn primary keys a lot so I know this should
just work, just need to set up your configuration
properly.  If you set up everything to use automatic
primary keys you should never need to even deal with
PKs at all.  Here's an example I used.  I can tarball
some of these for you if you need more details, just
send me an email address.

For a three table database:

Forums
Topics
Entrys

Where each Forum has one or more topics and each topic
has one or more Entrys.

Forums
  forum_id (PK)
  (etc.)

Topics
  topic_id (PK)
  forum_id (PK, FK to Forums)
  (etc.)

Entrys
  entry_id
  topic_id
  forum_id
  (etc.)

  PRIMARY KEY (entry_id, topic_id, forum_id)
  FOREIGN KEY (topic_id, forum_id) to Topics

You can express a relationship between Topics and
Entries like this (assuming your class files are
already properly set up:

__PACKAGE__->has_many(

  entrys => 'myschema::Entrys',
  {
    'foreign.topic_id' => 'self.topic_id',
    'foreign.forum_id' => 'self.forum_id',	
  },
);

The relationship from Entrys back to Topics is:

__PACKAGE__->belongs_to(

  topic	=> 'myschema::Topics',
  {
    'foreign.topic_id' => 'self.topic_id',
    'foreign.forum_id' => 'self.forum_id',
  },
);

Remember that you'll need to specify all the PKs like
so:

__PACKAGE__->set_primary_key("entry_id", "topic_id",
"forum_id");

If you use DBIx::Class::Schema::Loader to autogenerate
and save your class files you should find that it will
handle multicolumn PK relationships without any
trouble (at least it worked for me).

Good Luck!

--john

> 
> 
> 
> 		
>
___________________________________________________________
> 
> The all-new Yahoo! Mail goes wherever you go - free
> your email address from your Internet provider.
http://uk.docs.yahoo.com/nowyoucan.html>
_______________________________________________
> List:
>
http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN:
>
http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive:
http://www.mail-archive.com/dbix-class@lists.rawmode.org/



 
____________________________________________________________________________________
Cheap talk?
Check out Yahoo! Messenger's low PC-to-Phone call rates.
http://voice.yahoo.com



More information about the Dbix-class mailing list