Create new table in existing module without affecting existing table data

Question

I have my custom module myCustomModule in drupal 7 website along with installation file to create table.

I Have already saved lot of content in table but I need to create a new table in same module without affecting the previous table data.

if I add something new in .INSTALL file, I have to uninstall and install module which will remove the existing data content from my table.

Is there a way to create new table for existing module without affecting existing table data.

How do I update my module structure without affecting previous things.

[or last option for me is to create a new module and have separate table but I dont want to create so many modules ]

Please help , I need to do it for Drupal 7

EDIT

I followed this link and tried below and cleared cache after updating my code

/**
 * Create new database table {new_table}.
 */
function my_module_update_7101() {
 $schema['new_table'] = array(
     'description' => 'Table for storing information about static media asset .',
        'fields' => array(
            'aid' => array(
                'type' => 'serial',
                'not null' => TRUE,
                'description' => 'Primary Key: Unique aggregator static media asset ID.',
            ),
            'asset_title' => array(
                'type' => 'varchar',
                'length' => 255,
                'not null' => TRUE,
                'default' => '',
                'description' => 'Title of the static media asset title',
            ),
            'asset_file_path' => array(
                'type' => 'varchar',
                'length' => 255,
                'not null' => TRUE,
                'default' => '',
                'description' => 'CDN url path for asset.',
            ),
            'status' => array(
                'description' => 'status field for the static media assets',
                'type' => 'int',
                'length' => 1,
                'not null' => TRUE,
                'default' => 1,
            ),
        ),
        'primary key' => array('aid'),
    );

  db_create_table('new_table', $schema['new_table']);
}

It does not seem to update my table

Answer 1

Approach to generate a new table without affecting existing table's data.

Declare the table structure in hook_schema because for any new user who will install this module later should get this table so we have to code it here in hook_schema.

/** * hook_schema(). */

function my_module_schema() {
  $schema = array();
  $schema['new_table'] = array(
    'description' => 'Table for storing information about static media
asset .',
    'fields' => array(
      'aid' => array(
        'type' => 'serial',
        'not null' => TRUE,
        'description' => 'Primary Key: Unique aggregator static media asset ID.',
      ),
      'asset_title' => array(
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
        'description' => 'Title of the static media asset title',
      ),
      'asset_file_path' => array(
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
        'description' => 'CDN url path for asset.',
      ),
      'status' => array(
        'description' => 'status field for the static media assets',
        'type' => 'int',
        'length' => 1,
        'not null' => TRUE,
        'default' => 1,
      ),
    ),
    'primary key' => array('aid'),
  );
  return $schema;
}

For the users who have already installed the module and want to create a new table can write a hook_update_n() as below.

/** * hook_update_n(). */

function my_module_update_7001() {
  $schema = my_module_schema();
  if(!db_table_exists('new_table'))
    db_create_table('new_table', $schema['new_table']);
}

Here we will get all the schema and creating the 'new_table' by using db_create_table().

Hope this will resolve your issue. Thanks.

Answer 2

See hook_update_n(), particularly the Adding a new table heading.

1. Add the new table to mymodule_schema() in mymodule.install.
2. Create a mymodule_update_N() function to create mytable 2 with the Schema API function db_create_table():

Afterwards run database updates and it will create the new table (example.com/update.php or drush updb).