A subset of my database contains the tables contacts, networks and networkContacts which is my associative entity. I have defined it using a composite primary key composed of the primary keys of the first two tables. The tables are are defined as follows:
CREATE TABLE contacts ( cid char(88) NOT NULL, alias varchar(255) NOT NULL, PRIMARY KEY (cid) ) CREATE TABLE networks ( nid int(11) NOT NULL, fingerprint char(88) NOT NULL, port int(11) NOT NULL, network_alias varchar(255) NOT NULL, PRIMARY KEY (nid), UNIQUE KEY fingerprint (fingerprint), UNIQUE KEY port (port), UNIQUE KEY network_alias (network_alias) ) CREATE TABLE networkContacts ( nid int(11) NOT NULL, cid char(88) NOT NULL, PRIMARY KEY (nid,cid), CONSTRAINT networkContacts_ibfk_1 FOREIGN KEY (nid) REFERENCES networks (nid), CONSTRAINT networkContacts_ibfk_2 FOREIGN KEY (cid) REFERENCES contacts (cid) )
Is this correct and if so how should I correctly query the contacts table by inner joining it with the networks table as every example I have seen uses both fields in the primary key to conduct the join. As you can see I only have one field of the associative entity primary key in each of the other tables respectively so doing a join based on both components can't be done.