Re: implementation of a many-to-many relationship - Mailing list 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