Compound key in database schema

Question

I'm trying to define a compound key for a custom database table.

My first insert goes through OK but the following inserts fail because the PID value is the same.

What I'm trying to achieve is a state where the uniqueness of each insert is determined by a combination of the PID, QID and UID values of each insertion.

The following is my schema in my install script:

function module_schema() {

  $schema = array();

  $schema['module_records'] = array(
    'description' => '',
    'fields' => array(
      'pid' => array(
        'description' => 'PID',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'uid' => array(
        'description' => 'User ID',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'qid' => array(
        'description' => 'QID',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'first' => array(
        'description' => 'First option',
        'type' => 'text',
        'length' => 255,
        'not null' => FALSE,
      ),
      'second' => array(
        'description' => 'Second option',
        'type' => 'text',
        'length' => 255,
        'not null' => FALSE,
      ),
    ),
    'primary key' => array('pid'),
    'indexes' => array(
      'uid' => array('uid'),
      'qid' => array('qid'),
    );

  return $schema;
}

Answer 1

You may define 'type' => 'serial' if you want to auto increment your primary key.