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: Urgent, Many-to-many relationships.....HELP!!!!
Date: Tue, 26 Feb 2002 20:45:08 +0000 (GMT)
Message-ID: <200202262041.UAA11972@cs.may.ie> (raw)

Hi,

I'm a final year computer science student working on a database project. 
Basically I have two many-to-many relationships in my database but I don't know 
how to implement this in PostgreSQL. Here's an example of what I'm talkinng 
about.

I have a table voter and a table candidates. Because of the many-to-many 
relationship between these two table, I created a linking table c_voting. I 
reference both primary keys from voter and candidates to produce a composite 
primary key for c_voting.

see tables below

However when I go to stick data in c_voting, I get a referential integrity 
error, which says the primary key and foreign key don't match.

Basically the primary key in voter is an integer while the primary key in 
c_voting is (integer, integer) ie. the two foreign keys.

The same happens for the candidate table.

The theory is sound, it's just the syntax that's killing me

How do I get over this problem?

I have to hand this thesis up in 2 weeks, so I'm getting desperate for any help 
I can get.

Thanks in advance to those who may reply to this email

From

Shane.



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 combinations below seperately but got nowhere
	
	--primary key(v_number, c_number)
	
	--foreign key (v_number) references voter (v_number),
	--foreign key (c_number) references candidates(c_number)
);




view thread (2+ messages)  latest in thread

Message-ID: <200202262041.UAA11972@cs.may.ie>
Permalink:  ../200202262041.UAA11972@cs.may.ie/
Also on:    postgresql.org/message-id/200202262041.UAA11972@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: Urgent, Many-to-many relationships.....HELP!!!!
  In-Reply-To: <200202262041.UAA11972@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