Skip site navigation (1) Skip section navigation (2)

Re: optional reference

From: Mark Kelly <pgsql(at)wastedtimes(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: optional reference
Date: 2010-01-24 14:31:17
Message-ID: 201001241431.17415.pgsql@wastedtimes.net (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2010-01-24 18:26:50
Subject: Re: 8.5 beta manual
Previous:From: Rikard BosnjakovicDate: 2010-01-24 11:10:50
Subject: Re: Comparing times to "now + 45 seconds"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group