Thread: implementation of a many-to-many relationship
Hi,
I need to implement a many-to-many relationship in PostgreSQL but don't know how
to. I've trawled through the mailing lists but can't find anything, it's very
easy to do in Access so it can't be that hard in PostgreSQL.
I created a linking table between two tables (voter, candidates) called
c_voting, I then reference the two table's primary keys to create a composite
primary key for c_voting.
However when I go to input data into c_voting I get a referential integrity
error saying that the primary key of table voter doesn't match primary key of
c_voting (which it shouldn't).
I've tried everything I can think of for the last four days, but to no avail.
This is a central part of my thesis and I need to get it working as soon as
possible.
If anyone knows how to implement this I would be very very very grateful, I've
read all the documentation I can find but it didn't help.
Many thanks in advance for any advice that you can offer.
From
Shane.
here are the tables involved.
CREATE TABLE voter (v_number integer NOT NULL PRIMARY KEY, v_name
varchar(20),v_surname varchar(20),v_birth date,v_address varchar(50),v_marital varchar(10),v_job
varchar(15)
);
CREATE TABLE candidates (c_number integer NOT NULL PRIMARY KEY, c_name
varchar(20),c_surname varchar(20),c_party varchar(20),c_constituency varchar(35)
);
CREATE TABLE c_voting (v_number integer NOT NULL CONSTRAINT cvote_ref_voter REFERENCES voter
ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
c_number integer NOT NULL CONSTRAINT cvote_ref_can REFERENCES candidates ON UPDATE
CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
-- I tried these variations below but still got nothing working
--primary key(v_number, c_number)--foreign key (v_number) references voter (v_number),--foreign key (c_number)
referencescandidates(c_number)
);
On Wed, 27 Feb 2002, Dalton Shane wrote: > I need to implement a many-to-many relationship in PostgreSQL but don't know how > to. I've trawled through the mailing lists but can't find anything, it's very > easy to do in Access so it can't be that hard in PostgreSQL. > > I created a linking table between two tables (voter, candidates) called > c_voting, I then reference the two table's primary keys to create a composite > primary key for c_voting. > > However when I go to input data into c_voting I get a referential integrity > error saying that the primary key of table voter doesn't match primary key of > c_voting (which it shouldn't). Are you sure that's what it said? I could see it saying something like key referenced from c_voting not found in voter, but that'd make sense if you were trying to insert a v_number that didn't exist in voter. What version are you running? I tried the below in 7.2 and don't see anything particularly wierd happening. Can you give a sample set of inserts that you're trying and having fail?
On 27 Feb 2002 at 14:06, Dalton Shane wrote: > I need to implement a many-to-many relationship in PostgreSQL but don't > know how to. I've trawled through the mailing lists but can't find > anything, it's very easy to do in Access so it can't be that hard in > PostgreSQL. > > I created a linking table between two tables (voter, candidates) called > c_voting, I then reference the two table's primary keys to create a > composite primary key for c_voting. > > However when I go to input data into c_voting I get a referential integrity > error saying that the primary key of table voter doesn't match primary key > of c_voting (which it shouldn't). The actual error message would help us. > I've tried everything I can think of for the last four days, but to no > avail. > > This is a central part of my thesis and I need to get it working as soon as > possible. Yeah, well, we all have deadlines. > If anyone knows how to implement this I would be very very very grateful, > I've read all the documentation I can find but it didn't help. > > Many thanks in advance for any advice that you can offer. I created your tables. Then executed the following commands: test=# insert into voter (v_number) values (1); INSERT 1067044 1 test=# insert into candidates (c_number) values (123); INSERT 1067045 1 test=# insert into c_voting values (1, 123); INSERT 1067046 1 test=# select * from c_voting;v_number | c_number ----------+---------- 1 | 123 (1 row) I don't see the problem. Perhaps you should supply your example which fails. Mentioning the postgresql version might be helpful too. These tests were run on 7.2. -- Dan Langille The FreeBSD Diary - http://freebsddiary.org/ - practical examples
Dalton, > I need to implement a many-to-many relationship in PostgreSQL but > don't know how > to. I've trawled through the mailing lists but can't find anything, > it's very > easy to do in Access so it can't be that hard in PostgreSQL. No, it's not hard. It's very basic, which is why it's not specificallydocumented in the PostgreSQL docs. > I created a linking table between two tables (voter, candidates) > called > c_voting, I then reference the two table's primary keys to create a > composite > primary key for c_voting. This is correct. > However when I go to input data into c_voting I get a referential > integrity > error saying that the primary key of table voter doesn't match > primary key of > c_voting (which it shouldn't). Can you please post your exact INSERT statements and error message? From my perusal, the problem is with how you are insertingthe data,not your table design, which is fine (although I question your needfor the DEFERRABLE & INITIALLY DEFERREDstatements). -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
There should be three tables. Voter, Candidate, Voter_Candidate Voter ----- voter_id serial primary key name text Candidate --------- candidate_id serial primary key name text Voter_Candidate --------------- id serial primary key voter references Voter (voter_id) candidate references Candidate (candidate_id) Don't forget your table for the office or position the candidates are running for if thats necessary. On Wed, 2002-02-27 at 09:06, Dalton Shane wrote: > Hi, > > I need to implement a many-to-many relationship in PostgreSQL but don't know how > to. I've trawled through the mailing lists but can't find anything, it's very > easy to do in Access so it can't be that hard in PostgreSQL. > > I created a linking table between two tables (voter, candidates) called > c_voting, I then reference the two table's primary keys to create a composite > primary key for c_voting. > > However when I go to input data into c_voting I get a referential integrity > error saying that the primary key of table voter doesn't match primary key of > c_voting (which it shouldn't). > > I've tried everything I can think of for the last four days, but to no avail. > > This is a central part of my thesis and I need to get it working as soon as > possible. > > If anyone knows how to implement this I would be very very very grateful, I've > read all the documentation I can find but it didn't help. > > Many thanks in advance for any advice that you can offer. > > >From > > Shane. > > > here are the tables involved. > > CREATE TABLE voter ( > v_number integer NOT NULL > PRIMARY KEY, > > v_name varchar(20), > v_surname varchar(20), > v_birth date, > v_address varchar(50), > v_marital varchar(10), > v_job varchar(15) > ); > > CREATE TABLE candidates ( > c_number integer NOT NULL > PRIMARY KEY, > > c_name varchar(20), > c_surname varchar(20), > c_party varchar(20), > c_constituency varchar(35) > ); > > CREATE TABLE c_voting ( > v_number integer NOT NULL > CONSTRAINT cvote_ref_voter > REFERENCES voter > ON UPDATE CASCADE > ON DELETE CASCADE > DEFERRABLE > INITIALLY DEFERRED, > > c_number integer NOT NULL > CONSTRAINT cvote_ref_can > REFERENCES candidates > ON UPDATE CASCADE > ON DELETE CASCADE > DEFERRABLE > INITIALLY DEFERRED > > -- I tried these variations below but still got nothing working > > --primary key(v_number, c_number) > > --foreign key (v_number) references voter (v_number), > --foreign key (c_number) references candidates(c_number) > ); > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Jeff Self Information Technology Analyst Department of Personnel City of Newport News 2400 Washington Ave. Newport News, VA 23607 757-926-6930
On 27 Feb 2002 at 7:11, Stephan Szabo wrote: > Are you sure that's what it said? I could see it saying something like key > referenced from c_voting not found in voter, but that'd make sense if you > were trying to insert a v_number that didn't exist in voter. > > What version are you running? I tried the below in 7.2 and don't see > anything particularly wierd happening. > Can you give a sample set of inserts that you're trying and having fail? The original poster contacted me off list and said the problem has been resolved. -- Dan Langille The FreeBSD Diary - http://freebsddiary.org/ - practical examples