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@wastedtimes.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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-novice
>>
>
>
>