Why am I getting this error for the schema I define in my module?

Question

I'm building a module for Drupal 7, but I have troubles with my schema, and I don't understand why.

This is my schema.

$schema['table_name'] = array(
        'fields' => array(
            'id' => array(
                'type' => 'serial',
                'unsigned' => true,
                'not null' => true,
                'length' => 11
            ),
            'nid' => array(
                'type' => 'int',
                'unsigned' => true,
                'not null' => true,
                'length' => 11
            ),
            'oid' => array(
                'type' => 'int',
                'unsigned' => true,
                'not null' => true,
                'length' => 11
            ),
            'start_time' => array(
                'type' => 'datetime',
                'not null' => false
            ),
            'end_time' => array(
                'type' => 'datetime',
                'not null' => false
            ),
            'duration' => array(
                'type' => 'int',
                'unsigned' => true,
                'not null' => true,
                'length' => 11
            )
        ),
        'primary key' => array('id'),
        'unique keys' => array('id')
    );

I get this error.

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 'NULL DEFAULT NULL, end_time NULL DEFAULT NULL, duration INT unsigned NOT ' at line 5: CREATE TABLE {table_name} ( id INT unsigned NOT NULL auto_increment, nid INT unsigned NOT NULL, oid INT unsigned NOT NULL, start_time NULL DEFAULT NULL, end_time NULL DEFAULT NULL, duration INT unsigned NOT NULL, PRIMARY KEY (id), UNIQUE KEY 0 () ) ENGINE = InnoDB DEFAULT CHARACTER SET utf8; Array ( ) in db_create_table() (line 2717 of /Applications/MAMP/htdocs/project/www/includes/database/database.inc).

How can I fix this error?

Answer 1

I've managed to fix it. I've altered 2 things from my original schema, the first issue was that Drupal 7 no longer supports 'type' => 'datetime', instead use 'mysql_type' => 'datetime'. The second was 'unique keys' => array('id'), I removed this and everything works correctly.

Solution...

$schema['table_name'] = array(
        'fields' => array(
            'id' => array(
                'type' => 'serial',
                'unsigned' => true,
                'not null' => true,
                'length' => 11
            ),
            'nid' => array(
                'type' => 'int',
                'unsigned' => true,
                'not null' => true,
                'length' => 11
            ),
            'oid' => array(
                'type' => 'int',
                'unsigned' => true,
                'not null' => true,
                'length' => 11
            ),
            'start_time' => array(
                'type' => 'int',
                'mysql_type' => 'datetime',
                'size' => 'normal',
                'not null' => false
            ),
            'end_time' => array(
                'type' => 'int',
                'mysql_type' => 'datetime',
                'size' => 'normal',
                'not null' => false
            ),
            'duration' => array(
                'type' => 'int',
                'unsigned' => true,
                'not null' => true,
                'length' => 11
            )
        ),
        'primary key' => array('id'),
        'mysql_engine' => 'InnoDB'
    );

Answer 2

Try this for Drupal7,

'pubblish_date' => array(
  'description' => t('The pubblish date for the single news'),
  'type' => 'varchar',
  'mysql_type' => 'datetime',
  'not null' => FALSE,
),
'unpublish_date' => array(
  'description' => t('The unpublish date for the single news'),
  'type' => 'varchar',
  'mysql_type' => 'datetime',
  'not null' => FALSE,
),