A Drupal schema definition is an array structure representing one or more
tables and their related keys and indexes. A schema is defined by
hook_schema() which must live in your module's .install file.

This hook is called at install and uninstall time, and in the latter case, it
cannot rely on the .module file being loaded or hooks being known. If the
.module file is needed, it may be loaded with drupal_load().

The tables declared by this hook will be automatically created when the
module is first enabled, and removed when the module is uninstalled. This
happens before hook_install() is invoked, and after hook_uninstall() is
invoked, respectively.

By declaring the tables used by your module via an implementation of
hook_schema(), these tables will be available on all supported database
engines. You don't have to deal with the different SQL dialects for table
creation and alteration of the supported database engines.

See the Schema API Handbook at http://drupal.org/node/146843 for details on
schema definition structures. Note that foreign key definitions are for
documentation purposes only; foreign keys are not created in the database,
nor are they enforced by Drupal.

Return value

array
A schema definition structure array. For each element of the
array, the key is a table name and the value is a table structure
definition.

If I have multiple databases on my drupal site, and I want to create the table in a separate database from my default drupal database, do I just run the function db_set_active line code(in the link above) before the hook_schema function? Would I have to run the the same db_set_active function to switch back to my default database after the hook_schema function. I just want to make sure I don't end up with data complications with my databases. If you don't understand my question, let me know and I can rephrase it. Thanks in advance!

I have found that if you update hook_schema() while your module is enabled there seems to be an issue getting Drupal to generate your tables even after disabling and enabling your module. To get around this:

Merely disabling a module is not a destructive action, as (unless it was coded improperly) it will not delete the database tables associated with the module, nor will it delete any site-wide variables associated with it. This is so that you can disable a module, then enable it, and still have all your data intact. Having to completely uninstall a module in order to nuke its tables is proper behavior.

If you're a developer wanting to test that your schema works correctly, you may find it handy to use Drush to disable, uninstall, then reinstall your module in one go:

I can't use table name 'order'. Probably this was because word 'order' is MySQL reserved and can't be used as table name and drupal end with multiple sql errors. Strange situation. If module is installed with errors I can't properly uninstall. Tried multiple times while found my mistake.