Mysql Syntax Error in Drupal 7

Question

I am trying to define relationship between tables using foreign keys in drupal 7. I used hook_schema and hook_update function to define and update the schema. I am getting following errors.

$schema['relationship] = array(
  'description' => 'The table for employee organisation relationship',
  'fields' => array(
      'rid' => array(
          'description' => 'The primary Identifier for a Relationship.',
          'type' => 'serial',
          'unsigned' => TRUE,
          'not null' => TRUE,
      ),
      'uid' => array(
          'description' => 'The primary Identifier for User/Employee',
          'type' => 'int',
          'unsigned' => TRUE,
          'not null' => FALSE,
          'default' => 0,
      ),
      'oid' => array(
          'description' => 'The department Identifier of employee employed',
          'type' => 'int',
          'unsigned' => TRUE,
          'not null' => FALSE,
          'default' => 0,
  ),
  'indexes' => array(
      'uid' => array('uid'),
     'oid' => array('oid'),
  ),    
 'foreign keys' => array(
     'uid' => array(
         'table' => 'users',
          'columns' => array('uid' => 'uid')
     ),
      'oid' => array(
       'table' => 'organization',
         'columns' => array('oid' => 'oid')
   ),
 ),
  'primary key' => array('rid'),
  )
);

Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT NULL ) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COMMENT 'The table for' at line 5: CREATE TABLE {organisation} ( id INT unsigned NOT NULL auto_increment COMMENT 'The primary Identifier for a Relationship.', uid INT unsigned NULL DEFAULT 0 COMMENT 'The primary Identifier for User/Employee', oid INT unsigned NULL DEFAULT 0 COMMENT 'The department Identifier of employee employed', primary key DEFAULT NULL ) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COMMENT 'The table for employee organisation relationship'; Array ( ) in db_create_table() (line 2717 of C:\xampp\htdocs\tutumaudit\includes\database\database.inc).

David Jones David Jones · Accepted Answer · 2014-02-11T07:03:02

Well I sorted it out myself. I put those keys under the fields array. That was damn stupid and i needed 2 hours to find this out. The final code is below:

$schema['relationship] = array(
  'description' => 'The table for employee organisation relationship',
  'fields' => array(
      'rid' => array(
          'description' => 'The primary Identifier for a Relationship.',
          'type' => 'serial',
          'unsigned' => TRUE,
          'not null' => TRUE,
      ),
      'uid' => array(
          'description' => 'The primary Identifier for User/Employee',
          'type' => 'int',
          'unsigned' => TRUE,
          'not null' => FALSE,
          'default' => 0,
      ),
      'oid' => array(
          'description' => 'The department Identifier of employee employed',
          'type' => 'int',
          'unsigned' => TRUE,
          'not null' => FALSE,
          'default' => 0,
  ),  
  ),
'indexes' => array(
      'uid' => array('uid'),
     'oid' => array('oid'),
  ),    
 'foreign keys' => array(
     'uid' => array(
         'table' => 'users',
          'columns' => array('uid' => 'uid')
     ),
      'oid' => array(
       'table' => 'organization',
         'columns' => array('oid' => 'oid')
   ),
 ),
  'primary key' => array('rid'),
);

Mysql Syntax Error in Drupal 7

1 Answers