Received: from visi.net (arsenal.visi.net [206.246.194.60]) by postgresql.org (Postfix) with ESMTP id D7089475BB4 for ; Wed, 27 Feb 2002 13:04:48 -0500 (EST) X-Virus-Scanner: McAfee Virus Engine Received: from [216.54.6.254] (HELO localhost.localdomain) by visi.net (CommuniGate Pro SMTP 3.5.6) with ESMTP id 17251103; Wed, 27 Feb 2002 13:03:35 -0500 Subject: Re: implementation of a many-to-many relationship From: Jeff Self To: Dalton Shane Cc: pgsql-sql@postgresql.org In-Reply-To: <200202271402.OAA23102@cs.may.ie> References: <200202271402.OAA23102@cs.may.ie> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Evolution/1.0.2 Date: 27 Feb 2002 13:04:47 -0500 Message-Id: <1014833087.2404.10.camel@personnel_test> Mime-Version: 1.0 X-Archive-Number: 200202/437 X-Sequence-Number: 6858 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