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

Re: optional reference

From: Pushpendra Singh Thakur <thakur(at)corexprts(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Cc: Mark Kelly <pgsql(at)wastedtimes(dot)net>
Subject: Re: optional reference
Date: 2010-01-25 08:28:25
Message-ID: d6a413081001250028t29afb7fcsa7ccad7019220676@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
You are absolutely correct. My idea only works with unique fields.

2010/1/24 Mark Kelly <pgsql(at)wastedtimes(dot)net>

> 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
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>



-- 
Pushpendra Singh Thakur

COREEXPERTS Technologies Private Limited
Business Process Automation & IT Support Services
http://www.corexprts.com
Phone - 91-761-4070036
Fax - 91-761-4010530
SMS - 91-799-66554

In response to

Responses

pgsql-novice by date

Next:From: MattDate: 2010-01-25 19:10:04
Subject: Building from git repo on Win32
Previous:From: Thom BrownDate: 2010-01-24 20:42:55
Subject: Re: 8.5 beta manual

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