Multiple primary keys in a schema

Question

I have this schema which I need to define two primary keys; one is Drupal's 'vid' field and the other one is my site 'bid' field which is of auto increment type which in turn requires it to be a primary key: wtherwise I get MySQL error. I'm having trouble finding syntax for defining multiple primary keys in a Drupal schema. If anyone can help me out with the syntax, I pretty much appreciate it.

$schema['rft'] = array(
    'fields' => array(
        'vid' => array(
            'type' => 'int',
            'unsigned' => TRUE,
            'not null' => TRUE,
            'default' => 0,
        ),
        'nid' => array(
            'type' => 'int',
            'unsigned' => TRUE,
            'not null' => TRUE,
            'default' => 0,
        ),
        'bid' => array(
            'type' => 'serial',
            'size' => 'medium',
            'not null' => TRUE,                         
        ),

    ),
    'indexes' => array(
        'nid' => array('nid'),
    ),
    'primary key' => array('vid'),  //array('vid','bid') doesn't work
);

return $schema;
}

I am not sure it's possible to define multiple primary keys. In fact, I'm sure it's not possible. You *can* define a single primary key composed of multiple columns, but it's possible that Drupal doesn't have any syntax to express that... — Mike Caron, Aug 17 '10 at 04:05
@Mark Trapp : I was getting mysql error. I've tried once again after suggested by the member Dave and it's working again. Probably I made mistake somewhere. — Andrew, Aug 19 '10 at 21:10

score 9 · Accepted Answer · answered Aug 17 '10 at 15:41

Using the following worked just fine for me. Maybe it's a MySQL version-specific limitation? Can you report back the actual error message you got when Drupal tried to create this table?

$schema['rft'] = array(
    'fields' => array(
        'vid' => array(
            'type' => 'int',
            'unsigned' => TRUE,
            'not null' => TRUE,
            'default' => 0,
        ),
        'nid' => array(
            'type' => 'int',
            'unsigned' => TRUE,
            'not null' => TRUE,
            'default' => 0,
        ),
        'bid' => array(
            'type' => 'serial',
            'size' => 'medium',
            'not null' => TRUE,                         
        ),

    ),
    'indexes' => array(
        'nid' => array('nid'),
    ),
    'primary key' => array('vid', 'bid'),
);

It's working again. Feel like stupid now. I really don't know where I made mistake when I first try that syntax. I kept on getting mysql syntax error. Thanks very much dave. — Andrew, Aug 19 '10 at 21:08

Multiple primary keys in a schema

1 Answers1