title: Composite Primary Keys example in MySQL
body: <p>I have encountered MySQL itself recently and the topic of Composite Primary Keys in MySQL, especially how it is useful and what are its pros and cons from this <a href="http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx" rel="noreferrer">site</a></p>
  
  <p>I wanted to play with that, so I have created three tables in this fashion:</p>
  
  <pre><code>CREATE TABLE person(
      personId INT(11) NOT NULL,
      personName VARCHAR(20) NOT NULL,
      PRIMARY KEY(personId)
  )
  
  CREATE TABLE language(
      languageId INT(11) NOT NULL,
      languageName VARCHAR(20) NOT NULL,
      PRIMARY KEY(personId)
  )
  
  CREATE TABLE personLanguage(
      personId INT(11) NOT NULL,
      languageId INT(11) NOT NULL,
      description VARCHAR(20) NOT NULL,
      PRIMARY KEY(personId, languageId),
      FOREIGN KEY (personId) REFERENCES person(personId) ON UPDATE CASCADE ON DELETE CASCADE,
      FOREIGN KEY (languageId) REFERENCES language(languageId) ON UPDATE CASCADE ON DELETE CASCADE
  )
  </code></pre>
  
  <p>I can insert data into the person and language tables-straight forward, my questions are:</p>
  
  <ol>
  <li><p>For the personLanguage table do I need to insert only description column, while the other columns are automatically referenced, or do I need to insert the values for the other two columns in personLanguage table as well</p></li>
  <li><p>Is there a possibility to update the personId and languageId in personLanguage table automatically as soon as the data in other two tables are inserted, as far as I know when some update/delete is done in either of person or language tables it reflects the same on the two columns in personLanguage table</p></li>
  <li><p>How to fetch the data relating the three tables, for example I need to know which language does the person with personId=1 speaks? Is it also straight forward query using joins or is there some other way to do since I use composite primary keys</p></li>
  </ol>
  
  <p>Lots of questions bugging my mind and I could not really find a whole working example to check the exact pros and cons of using composite primary keys. In case if somebody could elaborate this using my example, would be really helpful.</p>
  
  <p>I know I have sort of asked some basic, some what makes no sense question, but please do bear me and throw some good light on this topic</p>