pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jeff Self <jself@nngov.com>
To: Dalton Shane <se401029@cs.may.ie>
Cc: pgsql-sql@postgresql.org
Subject: Re: implementation of a many-to-many relationship
Date: 27 Feb 2002 13:04:47 -0500
Message-ID: <1014833087.2404.10.camel@personnel_test> (raw)
In-Reply-To: <200202271402.OAA23102@cs.may.ie>
References: <200202271402.OAA23102@cs.may.ie>

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




view thread (6+ messages)  latest in thread

Message-ID: <1014833087.2404.10.camel@personnel_test>
Permalink:  ../1014833087.2404.10.camel@personnel_test/
Also on:    postgresql.org/message-id/1014833087.2404.10.camel@personnel_test

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jself@nngov.com, se401029@cs.may.ie
  Subject: Re: implementation of a many-to-many relationship
  In-Reply-To: <1014833087.2404.10.camel@personnel_test>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox