Re: implementation of a many-to-many relationship

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