MySql create simple foreign key between two table

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…)

Most likely your field definitions are not identical. Can you post the exact commands used to create the two tables? — Atli, Jan 30 '16 at 04:23
@GauravLad no sir, my tables empty now, i want to create only simple relation ship — , Jan 30 '16 at 04:26
ALTER TABLE amount_repositories ADD CONSTRAINT amount_repositories_user_id_foreign FOREIGN KEY (user_id) REFERENCES user_amounts_account(id) its working n phpmyadmin — Vasim Shaikh, Jan 30 '16 at 04:34
@VasimVanzara mysql commands not working for me, for example: http://paste.debian.net/377565/ — , Jan 30 '16 at 05:07
@Atli can you test this mysql commands: http://paste.debian.net/377565/ — , Jan 30 '16 at 05:11

score 0 · Answer 1 · answered Jan 30 '16 at 04:27

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');
});

score 0 · Answer 2 · answered Jan 30 '16 at 06:34

Create PHP class file create_user_amounts_account_table.php and place the following code

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class UserAmountsAccount extends Migration
{

   /**
   * Run the migrations.
   *
   * @return void
   */
   public function up()
   {
    Schema::create('UserAmountsAccount', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('user_id');
        $table->string('amount');
        $table->boolean('type');
        $table->timestamps();
    });
}

   /**
   * Reverse the migrations.
   *
   * @return void
   */
  public function down()
  {
    Schema::drop('UserAmountsAccount');
  }

}

Create PHP class file create_amount_repositories_table.php and place the following code

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class AmountRepositories extends Migration
{

   /**
   * Run the migrations.
   *
   * @return void
   */
   public function up()
   {
    Schema::create('AmountRepositories', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('user_id');
        $table->string('amount');
        $table->timestamps();
        $table->integer('user_id')->unsigned();
        $table->foreign('user_id')
            ->references('id')
            ->on('user_amounts_account')->onDelete('cascade');
    });
}

   /**
   * Reverse the migrations.
   *
   * @return void
   */
  public function down()
  {
    Schema::drop('AmountRepositories');
  }

}

and finally run the migration. That's it !

MySql create simple foreign key between two table

2 Answers2