Re: optional reference

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