The table (foreign) must be created before the key could be added. So the order of migration is important ...
Jul 14, 2016
7
Level 1
String as foreign key doesn't work
Hello I'm trying create relations on 2 tables on string keys, that's my migrations:
<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateVerticesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('vertices', function (Blueprint $table) {
$table->string("name")->primary();
$table->string("status");
$table->integer("level");
});
Schema::table("vertices", function($table) {
$table->foreign("name")->references("vertex_a")->on("edges");
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('vertices');
}
}
<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateEdgesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('edges', function (Blueprint $table) {
$table->increments("id");
$table->string("vertex_a")->unique();
$table->string("vertex_b");
$table->integer("weight_a");
$table->integer("weight_b")->nullable()->default(NULL);
$table->integer("status");
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('edges');
}
}
When I turn on my migrations i get error:
[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constrain
t (SQL: alter table `vertices` add constraint `vertices_name_foreign`
foreign key (`name`) references `edges` (`vertex_a`))
Level 47
http://stackoverflow.com/questions/19299874/can-i-use-varchar-as-the-primary-key
Why a string? Comparing strings is more expensive then integers. It's outside some of the fundamentals of db schema for rational db's
Setting an index for searching/finding yes, but actually connecting two tables via string isn't efficent.
I hate saying this: it's not a good practice.
Please or to participate in this conversation.