<?php 
$installer = $this;
$installer->startSetup();
$installer->run("
    CREATE TABLE `{$installer->getTable('globalthinking_academy/course')}` (
      `course_id` int(11) unsigned NOT NULL auto_increment,
      `store_id` smallint(5) unsigned DEFAULT NULL, /*Note: store_id must allow NULL if we SET NULL on delete */
      
      `course_number` varchar(20) NOT NULL,
      `course_name` text,
      ...
      `status` varchar(20) NOT NULL default 'pending',
      `created_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
      `updated_at` timestamp NOT NULL,
      PRIMARY KEY  (`course_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
");

$installer->getConnection()->addKey($installer->getTable('globalthinking_academy/course'),'FK_COURSE_STORE','store_id');
$installer
    ->getConnection()
    ->addConstraint(
        'FK_COURSE_STORE',
        $installer->getTable('globalthinking_academy/course'), 
        'store_id',
        $installer->getTable('core/store'), 
        'store_id',
        'set null', /*Note: store_id column must allow NULL if we SET NULL on delete */
        'cascade'
);

$installer->endSetup();

I was recently working on a project in Magento, and was having problems adding a MySQL table with a foreign key constraint referencing the core_stores table.

It turns out that my problem had to do with the NOT NULL attributes of the store_id column and the ON DELETE SET NULL behavior of the foreign key constraint. If the constraint is ON DELETE SET NULL, then your column CANNOT be NOT NULL; otherwise, MySQL will give an error message something like: MySQL Error Nr. 1005 Can't create table ______ (errno: 150)