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)
referencescandidates(c_number)
);
