Re: implied foreign keys

[prev] [thread] [next] [Date index for 2004/10/01]

--- Tony Bowden <tony-cdbitalk@xxxxx.xxx> wrote:

> On Fri, Oct 01, 2004 at 02:18:33PM +0100, josh_f_peterson@xxxxx.xxx
> wrote:
> > CREATE TABLE well (
> >   well_id integer NOT NULL,
> >   barcode VARCHAR2(12) NOT NULL,
> >   other columns here,
> >   CONSTRAINT well_PK PRIMARY KEY (well_id)
> > );
> > CREATE TABLE sample (
> >   sample_id integer NOT NULL,
> >   barcode VARCHAR2(12) NOT NULL,
> >   other columns here,
> >   CONSTRAINT sample_PK PRIMARY KEY (sample_id)
> > );
> > I'd like to join them using the barcode column these share, and so
> I set up the classes for the two as if for a regular foreign key:
> > 
> > # In the well class
> > __PACKAGE__->has_a( barcode => 'DB::sample');
> 
> That's not going to work. This is saying that the value in the
> 'barcode'
> column of 'well' is the primary key of the 'sample' table.
> 

Let me see if I understand you clearly.  Your statement implies that
has_a always points to a primary key.  What I've asked is if you can
imply foreign key relationships on non-PK columns.  If your statement's
accurate, then there's no way to handle foreign keys on non-PK columns.

> Is the barcode column in 'sample' unique? If so should it not be the
> PK
> of that table? If not, then the relationship couldn't possibly work
> like
> that anyway...

No, the barcode column should not be a PK; samples have multiple unique
keys since barcodes come from multiple sources, internal and external,
although I've only drawn out one of these here.

In any case, it sounds as if you're saying joins in CDBI must occur on
PKs.

Thanks for the feedback.

-Josh

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com