Re: optional reference - Mailing list pgsql-novice
From | bill house |
---|---|
Subject | Re: optional reference |
Date | |
Msg-id | 4B5E7203.4040307@bellsouth.net Whole thread Raw |
In response to | Re: optional reference (Pushpendra Singh Thakur <thakur@corexprts.com>) |
List | pgsql-novice |
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 >> > > >
pgsql-novice by date: