implied foreign keys

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

I have two tables with the following general format in separate Oracle schemas:

CREATE TABLE well (
  well_id integer NOT NULL,
  barcode VARCHAR2(12) NOT NULL,
  other columns here,
  CONSTRAINT well_PK PRIMARY KEY (well_id)
);
CREATE UNIQUE INDEX well_UK01 ON well (barcode);

CREATE TABLE sample (
  sample_id integer NOT NULL,
  barcode VARCHAR2(12) NOT NULL,
  other columns here,
  CONSTRAINT sample_PK PRIMARY KEY (sample_id)
);
CREATE UNIQUE INDEX sample_UK01 ON sample (barcode);

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',
);

# In the sample class
__PACKAGE__->has_many(
    wells   => 'DB::well' => 'barcode',
);

The code compiles and runs, but no join occurs.  If, instead of an implied foreign key, I set up table joins (across or within schemas, it doesn't matter) on a primary key, the join works appropriately.

I assume I'm simply missing something in the configuration of an implied foreign key.  Any help would be appreciated.

Thanks,

-Josh