MySql在两个表之间创建简单的外键

Question

in this simple two table i want to create foreign key between amount_repositories.user_id and user_amounts_account.id, but i get error:

Mysql create tables:

CREATE TABLE IF NOT EXISTS `report_transactions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `amount` int(11) NOT NULL,
  `order_id` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `payment_order_id` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `token` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `reference_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `given_reference_id` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  `redirect_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `type_result` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `result` tinyint(4) NOT NULL,
  `customer_id` int(10) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `report_transactions_customer_id_foreign` (`customer_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=232 ;

CREATE TABLE IF NOT EXISTS `amount_repositories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `amount` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `amount_repositories_user_id_foreign` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `user_amounts_account` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `amount` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `type` tinyint(4) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;


ALTER TABLE `amount_repositories`
  ADD CONSTRAINT `amount_repositories_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `report_transactions` (`customer_id`);

ALTER TABLE `user_amounts_account`
  ADD CONSTRAINT `user_amounts_account_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `amount_repositories`
  ADD CONSTRAINT `amount_repositories_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `user_amounts_account` (`id`);

Error:

#1005 - Can't create table 'test.#sql-4c2_403' (errno: 150) (Details…) 

Answer 1

Generally,Code something like this,

Schema::create('gigs', function($table)
{
    $table->increments('gig_id');

    $table->dateTime('gig_startdate');

    $table->integer('band_id')->unsigned();
    $table->integer('stage_id')->unsigned();
});

Schema::table('gigs', function($table)
{
    $table->foreign('band_id')
        ->references('band_id')->on('bands')
        ->onDelete('cascade');

    $table->foreign('stage_id')
        ->references('stage_id')->on('stages')
        ->onDelete('cascade');
});