Received: from cs.may.ie (l3ult25.cs.may.ie [149.157.246.225]) by postgresql.org (Postfix) with ESMTP id 61F8B4759B3 for ; Tue, 26 Feb 2002 15:46:47 -0500 (EST) Received: from l2sol29 (l2sol29 [149.157.244.139]) by cs.may.ie (8.9.3/8.9.1) with SMTP id UAA11972 for ; Tue, 26 Feb 2002 20:41:13 GMT Message-Id: <200202262041.UAA11972@cs.may.ie> Date: Tue, 26 Feb 2002 20:45:08 +0000 (GMT) From: Dalton Shane Reply-To: Dalton Shane Subject: Urgent, Many-to-many relationships.....HELP!!!! To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: TEXT/plain; charset=us-ascii Content-MD5: a2zPglbhr10iSt9GNeNp+Q== X-Mailer: dtmail 1.3.0 @(#)CDE Version 1.3.2 SunOS 5.7 i86pc i386 X-Archive-Number: 200203/4 X-Sequence-Number: 6886 Hi, I'm a final year computer science student working on a database project. Basically I have two many-to-many relationships in my database but I don't know how to implement this in PostgreSQL. Here's an example of what I'm talkinng about. I have a table voter and a table candidates. Because of the many-to-many relationship between these two table, I created a linking table c_voting. I reference both primary keys from voter and candidates to produce a composite primary key for c_voting. see tables below However when I go to stick data in c_voting, I get a referential integrity error, which says the primary key and foreign key don't match. Basically the primary key in voter is an integer while the primary key in c_voting is (integer, integer) ie. the two foreign keys. The same happens for the candidate table. The theory is sound, it's just the syntax that's killing me How do I get over this problem? I have to hand this thesis up in 2 weeks, so I'm getting desperate for any help I can get. Thanks in advance to those who may reply to this email From Shane. 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 combinations below seperately but got nowhere --primary key(v_number, c_number) --foreign key (v_number) references voter (v_number), --foreign key (c_number) references candidates(c_number) );