Re: optional reference

Thanks for the help guys,

Since I really am a novice, I will have to digest this. If I learn
anything to share on this subject, I will post again.

Thanks again

Bill House

Pushpendra Singh Thakur wrote:
> 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
>>
>
>
>