Laravel 5.4 MySQL 5.6.35 - The famous # 1215 - Can not add foreign key constraint

Question

Laravel 5.4 MySQL 5.6.35 - The famous # 1215 - Can not add foreign key constraint

Navigation

#1 by (0 votes)

2

Creating a migration in Laravel 5.4 with MySQL 5.6 I can add an external reference perfectly if it is on the Id of another table, but I can not add it on a string index.

This works well on the Id field

CREATE TABLE 'stock' (
  'id' int(10) unsigned NOT NULL AUTO_INCREMENT,
  'component_id' int(10) unsigned NOT NULL,
  'code' varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  'quantity' int(11) NOT NULL,
  'created_at' timestamp NULL DEFAULT NULL,
  'updated_at' timestamp NULL DEFAULT NULL,
  PRIMARY KEY ('id'),
  UNIQUE KEY 'stock_component_code_code_unique' ('component_code','code'),
  KEY 'stock_component_id_foreign' ('component_id'),
  CONSTRAINT 'stock_component_id_foreign' FOREIGN KEY ('component_id') REFERENCES 'components' ('id') ON DELETE CASCADE
)

But I want about the VARCHAR field

 CREATE TABLE 'stock' (
  'id' int(10) unsigned NOT NULL AUTO_INCREMENT,
  'component_id' int(10) unsigned NOT NULL,
  'code' varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  'quantity' int(11) NOT NULL,
  'created_at' timestamp NULL DEFAULT NULL,
  'updated_at' timestamp NULL DEFAULT NULL,
  PRIMARY KEY ('id'),
  UNIQUE KEY 'stock_component_code_code_unique' ('component_code','code'),
  KEY 'stock_component_id_foreign' ('component_id'),
  CONSTRAINT 'stock_component_code_foreign' FOREIGN KEY ('component_code') REFERENCES 'components' ('code') ON DELETE CASCADE
)

I get

#1215 - Cannot add foreign key constraint

What can I do? It's not Laravel's subject, I get the error in MySQL directly.

RESOLVED Separating in two Schema, the creation of the table and in another the creation of indices. So:

        Schema::create('existencias', function (Blueprint $table) {
        $table->engine = 'InnoDB';

        $table->increments('id');
        $table->string('componente_code',20);     // FK 
        $table->string('site',20);                // Código de ubicacion
        $table->string('idlabel',20)->default(''); // Identificador visual
        $table->integer('cantidad')->unsigned()->default(1);  // Cantidad
        $table->integer('cargas')->unsigned()->default(0);    // Ciclos de cargas (Baterías)
        $table->json('info')->nullable();         // Información Extra  (Baterías)
        $table->char('estado', 1)->default("I");  // Tipo DIA - Descartado/Inactivo/Activo - Rojo/Amarill/Verde 
        //TS
        $table->timestamps();                     // Modified At - fecha de última modificación
    });

    Schema::table('existencias', function($table) {
        $table->unique([
            'componente_code', 
            'site',
            'idlabel',
            ]);
        $table->foreign('componente_code')
          ->references('code')->on('componentes')
          ->onDelete('cascade');
    });

Thanks for your answers

mysql sql laravel

asked by Santi 04.09.2017 в 19:16

source

1 answer

Enable comments in custom post type PHP - Browse large CSV file with function

score 0 · Answer 1

For the case of foreign keys of the varchar type, the following example can be followed, where a repair has an associated terminal:

-in the definition of migration, the table with the primary key of the varchar type (terminals), can be defined as follows (in this case, code would be the primary key):

public function up()
{
    Schema::create('terminales', function (Blueprint $table) {

        $table->string('codigo');
        $table->primary('codigo');  

        $table->string('direccion');

-in the model (Terminal), perform the following definition (setting mainly primaryKey and incrementing variables):

class Terminal extends Model
{
    protected $primaryKey = "codigo";

    public $incrementing = false;

    protected $table = "terminales";

    protected $fillable = ['codigo', 'direccion', 'telefono', ];

-and finally, in the definition of the migration, of the table that will instantiate the aforementioned (repairs), it can be defined as follows:

public function up()
{
    Schema::create('reparaciones', function (Blueprint $table) {
        $table->increments('id');

        $table->string('clave');

        $table->string('id_terminal');
        $table->foreign('id_terminal')->references('nro')->on('terminales');

Greetings