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