pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedimplementation 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