http://www.perlmonks.org?node_id=461888

silent11 has asked for the wisdom of the Perl Monks concerning the following question:

After spending about an hour attempting to figure out why Class::DBI was croaking on a package that was defining a table with 2 foreign keys I realized that multiple foreign keys in one table isn't supported by Class::DBI.

Well, that's fine -- I'm just giving CDBI a test run, and I'm glad I found this little quirk out early in my usage with it.

In this test application (a bookmarks/links app) I had 3 tables, `users`, `category`, and `links`. The link table contained the columns `id`,`category_id`,`user_id`,`url`,etc...

My question is, why are multiple foreign keys such an evil thing? And what would be a better way to design this schema?




Update:Okay, I got it working, despite the problme I *thought* I had. reading this helped me out.

-silent11
Spread Firefox

Janitored by Arunbear - moved from Meditations to SOPW. Also removed 'OT?' prefix from title.

Replies are listed 'Best First'.
Re: Class::DBI and multiple foreign keys
by perrin (Chancellor) on May 31, 2005 at 01:58 UTC
    Sounds like you got confused between multi-column foreign keys and multiple foreign keys in one table. Class::DBI does not support the former, although not for any special reason -- it simply hasn't been built yet.
      it simply hasn't been built yet.
      It's quite possible that because one of my clients needs MCFK support that they'll be funding me to complete this (perhaps even in the next few weeks), and of course make the result open-source in the CPAN. Yeay!

      -- Randal L. Schwartz, Perl hacker
      Be sure to read my standard disclaimer if this is a reply.

Re: Class::DBI and multiple foreign keys
by monarch (Priest) on May 31, 2005 at 04:42 UTC
    (this is just an aside comment)

    After reading an article on www.perl.com I was quite enthusiastic about Class::DBI and wanted to use it for projects, however trying to install it was somewhat difficult - it had numerous dependancies and took me quite a while to get going.

    One other package is like this: DateTime. I once went on a world trip and using this fantastic module I calculated the local times, and sunrises and sunsets on each day in each city that I would be in! Fabulous! However installing this module is a huge effort because of the sheer number of dependencies.

    It is sad because I would like to use these modules more often but just fear for needing to run my application on a system where installing a large number of modules may not be an option (such as websites).

      However installing this module is a huge effort because of the sheer number of dependencies.
      Only if you make it one. CPAN/PPM/CPANPLUS all make it significantly faster.

      MJD says "you can't just make shit up and expect the computer to know what you mean, retardo!"
      I run a Win32 PPM repository for perl 5.6.x and 5.8.x -- I take requests (README).
      ** The third rule of perl club is a statement of fact: pod is sexy.