title: SQL Join Table - Does it require a primary key at all or just unique keys?
body: <p>I have created an application for the CakePHP framework which uses a join table. </p>
  
  <p>I am unsure as to whether it is neccessary that I need a primary key to uniquley identify each row for the join table, as shown in the first block of SQL. </p>
  
  <p>Do the two fields need to be set as unique keys or can they both be set as primary keys and I remove id as the primary key?</p>
  
  <p>I was also asked why declaring atomic primary keys using a table constraint rather
  than a column constraint, does this mean I shouldn't set unique keys for a join table?</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `categories_invoices` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `category_id` int(11) NOT NULL,
    `invoice_id` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `category_id` (`category_id`,`invoice_id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=163 ;
  </code></pre>
  
  <p>I was thinking the solution is possibly to set both keys as unique and remove the primary key as shown here:</p>
  
  <pre><code> CREATE TABLE IF NOT EXISTS `categories_invoices` (
        `category_id` int(11) NOT NULL,
        `invoice_id` int(11) NOT NULL,
        UNIQUE KEY `category_id` (`category_id`,`invoice_id`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>I did in fact test deleting the primary key 'id' for the join table leaving only 'category_id' and 'invoice_id' and the application still worked. This has left both fields as unique fields within the join table. Is this in fact the correct practice?</p>