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,

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.

  • 4 unique keys in a 4 column table? This seems quite unreasonable. – Rick James May 31 at 20:36
  • The nid is the primary key so it must be unique, fingerprint must be unique being a hash of a public key where each network has a distinct set of keys, the port must obviously be different for each network and the network_alias must be different to allow humans to differentiate. – nrmad Jun 1 at 9:51

In order to JOIN contacts to networks you need to first JOIN to networkContacts.

select *
from contacts a
    inner join networkContacts b
        on a.cid=b.cid
    inner join networks c
        on c.nid=b.nid
| improve this answer | |
  • Alright thanks, I thought it might be bad form to perform individual joins based on just part of the primary key but great – nrmad May 25 at 12:20
  • The PK on networkContacts is there to prevent duplicate pairs of (nid,cid) within the table. It could just as easily be a UNIQUE constraint (since the columns are defined as NOT NULL) – Michael Kutz May 25 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.