pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dalton Shane <se401029@cs.may.ie>
To: pgsql-sql@postgresql.org
Subject: implementation of a many-to-many relationship
Date: Wed, 27 Feb 2002 14:06:17 +0000 (GMT)
Message-ID: <200202271402.OAA23102@cs.may.ie> (raw)

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)
);




view thread (6+ messages)  latest in thread

Message-ID: <200202271402.OAA23102@cs.may.ie>
Permalink:  ../200202271402.OAA23102@cs.may.ie/
Also on:    postgresql.org/message-id/200202271402.OAA23102@cs.may.ie

 · 

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: se401029@cs.may.ie
  Subject: Re: implementation of a many-to-many relationship
  In-Reply-To: <200202271402.OAA23102@cs.may.ie>

* 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