pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
implementation of a many-to-many relationship
6+ messages / 5 participants
[nested] [flat]

* implementation of a many-to-many relationship
@ 2002-02-27 14:06  Dalton Shane <se401029@cs.may.ie>
  0 siblings, 5 replies; 6+ messages in thread

From: Dalton Shane @ 2002-02-27 14:06 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: implementation of a many-to-many relationship
@ 2002-02-27 15:11  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Dalton Shane <se401029@cs.may.ie>
  4 siblings, 0 replies; 6+ messages in thread

From: Stephan Szabo @ 2002-02-27 15:11 UTC (permalink / raw)
  To: Dalton Shane <se401029@cs.may.ie>; +Cc: pgsql-sql

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?





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: implementation of a many-to-many relationship
@ 2002-02-27 15:15  Dan Langille <dan@langille.org>
  parent: Dalton Shane <se401029@cs.may.ie>
  4 siblings, 0 replies; 6+ messages in thread

From: Dan Langille @ 2002-02-27 15:15 UTC (permalink / raw)
  To: Dalton Shane <se401029@cs.may.ie>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: implementation of a many-to-many relationship
@ 2002-02-27 16:25  Josh Berkus <josh@agliodbs.com>
  parent: Dalton Shane <se401029@cs.may.ie>
  4 siblings, 0 replies; 6+ messages in thread

From: Josh Berkus @ 2002-02-27 16:25 UTC (permalink / raw)
  To: Dalton Shane <se401029@cs.may.ie>; pgsql-sql

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 specifically
 documented 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 inserting the data,
 not your table design, which is fine (although I question your need
 for the DEFERRABLE & INITIALLY DEFERRED statements).

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: implementation of a many-to-many relationship
@ 2002-02-27 18:04  Jeff Self <jself@nngov.com>
  parent: Dalton Shane <se401029@cs.may.ie>
  4 siblings, 0 replies; 6+ messages in thread

From: Jeff Self @ 2002-02-27 18:04 UTC (permalink / raw)
  To: Dalton Shane <se401029@cs.may.ie>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: implementation of a many-to-many relationship
@ 2002-02-27 18:07  Dan Langille <dan@langille.org>
  parent: Dalton Shane <se401029@cs.may.ie>
  4 siblings, 0 replies; 6+ messages in thread

From: Dan Langille @ 2002-02-27 18:07 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2002-02-27 18:07 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-27 14:06 implementation of a many-to-many relationship Dalton Shane <se401029@cs.may.ie>
2002-02-27 15:11 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-02-27 15:15 ` Dan Langille <dan@langille.org>
2002-02-27 16:25 ` Josh Berkus <josh@agliodbs.com>
2002-02-27 18:04 ` Jeff Self <jself@nngov.com>
2002-02-27 18:07 ` Dan Langille <dan@langille.org>

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