Re: optional reference

Hi.

On Sunday 24 Jan 2010 at 10:00 Pushpendra Singh Thakur wrote:

> create a third table to store your relations only.
> Relation table C
> create table C(
> a_col1 char(8) references a(column_1),
> b_col1 char(8) references b(column_1))
>
> Both the tables will be independent (a and b) i mean they will not have any
> direct relations.

This is certainly the approach I'd use (it lets you link any row in a to any
combination of rows in b), but your suggestion won't work for the example in
the original question, since a(column_1) contains non-unique values. The
relations table should only reference primary keys in the other tables.

I'd add a serial primary key to both tables and use that in the third table,
but the poster may have a need for the compound text key on b that prevents
this.

Anyway, assuming PK changes:

CREATE TABLE a (
  row_id SERIAL PRIMARY KEY,
  column_1 character(8),
  column_2 character(2),
  column_3 character(40)
  );

CREATE TABLE b (
  row_id SERIAL PRIMARY KEY,
  column_1 character(8),
  column_2 character(2),
  column_3 character(40)
  );

-- Added unique constraint to stop you accidentally adding the same link
-- twice, and some cascades to maintain the link table integrity.
CREATE TABLE c (
  link_a INTEGER REFERENCES a(row_id) ON UPDATE CASCADE ON DELETE CASCADE,
  link_b INTEGER REFERENCES b(row_id) ON UPDATE CASCADE ON DELETE CASCADE,
  UNIQUE (link_a,link_b)
  );

INSERT INTO a (column_1,column_2,column_3) VALUES
   ('20901234', '01', 'This is a the first row'),
   ('20901234', '01', 'This is the second row'),
   ('20901234', '01', 'This is the third row'),
   ('20901235', '01', 'This is the fourth row'),
   ('20901236', '01', 'This is the fifth row'),
   ('20901236', '01', 'This is the sixth row'),
   ('20901237', '01', 'This is the seventh row'),
   ('20901238', '01', 'This is the eighth row');

  INSERT INTO b (column_1,column_2,column_3) VALUES
   ('20901234', '01', 'Footnote #1'),
   ('20901234', '02', 'other stuff'),
   ('20901237', '01', 'Footnote');

-- This assumes the PKs started counting from 1 (default)
INSERT INTO c VALUES
   ('1','1'),
   ('2','1'),
   ('3','1'),
   ('7','3');

-- get all from b linked to row 1 in a
SELECT b.* FROM b,c WHERE b.row_id = c.link_b AND c.link_a = '1';

Cheers,

Mark