Syntax error or access violation: 1059 Identifier name '' is too long lumen

Question

Syntax error or access violation: 1059 Identifier name '' is too long lumen

Navigation

#1 by (3 votes)
#2 by (1 votes)

0

I'm migrating to the database with a lumen, and I get the error that the primary key is too long. What I want is to put the three columns as composite keys.

    public function up()
{
   if(!Schema::hasTable('asignaturas_alumno')){
        Schema::create('asignaturas_alumno',function (Blueprint $table){
            $table->engine = 'InnoDB';
            $table->bigInteger('id_curso_asignatura');
            $table->bigInteger('id_alumno');
            $table->bigInteger('id_curso_escolar');
            $table->primary(['id_alumno','id_curso_escolar','id_curso_asignatura']);
        });
   }

}

This error:

  [Illuminate\Database\QueryException]
 SQLSTATE[42000]: Syntax error or access violation: 1059 Identifier name
'asignaturas_alumno_id_alumno_id_curso_escolar_id_curso_asignatura_primary' is 
too long (SQL: alter tabl
e 'asignaturas_alumno' add primary key 'asignaturas_alumno_id_alumno_id_curso_escolar_id_curso_asignatura_primary'('id_alumno', 'id_curso_escolar', 'id_curso_asignatura'))



[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1059 Identifier name        'asignaturas_alumno_id_alumno_id_curso_escolar_id_curso_asignatura_primary' is   too long



 [PDOException]
 SQLSTATE[42000]: Syntax error or access violation: 1059 Identifier name 'asignaturas_alumno_id_alumno_id_curso_escolar_id_curso_asignatura_primary' is   too long

mysql php laravel lumen

asked by Jose Yeste 29.03.2017 в 15:13

source

2 answers

Create routes for node and Express server Deserialize JSON with Newtonsoft for different answers

score 3 · Answer 1

Column names, index names, table names ... and others are limited to 64 characters in MySQL.

You have somewhere an identifier (apparently a primary key name) called asignaturas_alumno_id_alumno_id_curso_escolar_id_curso_asignatura_primary that has 73 characters and therefore violates that MySQL rule.

Hence the error:

SQLSTATE [42000]: Syntax error or access violation: 1059 Identifier yam 'asignaturas_alumno_id_alumno_id_curso_escolar_id_curso_asignatura_primary' is too long (SQL: alter tabl e asignaturas_alumno add primary key asignaturas_alumno_id_alumno_id_curso_escolar_id_curso_asignatura_primary ( id_alumno , id_curso_escolar , id_curso_asignatura ))

Generally the primary key names are short, something like pk_nombretabla ;

See: link

Solution

Assign a shorter name to the primary key in question.

score 1 · Answer 2

As @ A.Cedano says, your problem is that the primary key has a name too long.

Clearly, when you declare

$table->primary(['id_alumno','id_curso_escolar','id_curso_asignatura']);

Underneath Laravel or Lumen deduces the name of the key using the concatenation of the names of each field, separated with a _ and adding the suffix "_primary".

The solution would be to name your primary key yourself instead of letting the framework choose that name for you.

I do not use Laravel but as I noticed that $table is an instance of class Blueprint , and that class Blueprint is in the repo laravel / framework , so why? curiosity I searched for the Blueprint::primary method of the class, and found that this one accepts as the second parameter the name of the key .

/**
 * Specify the primary key(s) for the table.
 *
 * @param  string|array  $columns
 * @param  string  $name
 * @param  string|null  $algorithm
 * @return \Illuminate\Support\Fluent
 */
public function primary($columns, $name = null, $algorithm = null)
{
    return $this->indexCommand('primary', $columns, $name, $algorithm);
}

This method calls the indexCommand method:

/**
 * Add a new index command to the blueprint.
 *
 * @param  string        $type
 * @param  string|array  $columns
 * @param  string        $index
 * @param  string|null   $algorithm
 * @return \Illuminate\Support\Fluent
 */
protected function indexCommand($type, $columns, $index, $algorithm = null)
{
    $columns = (array) $columns;
    // If no name was specified for this index, we will create one using a basic
    // convention of the table name, followed by the columns, followed by an
    // index type, such as primary or index, which makes the index unique.
    $index = $index ?: $this->createIndexName($type, $columns);
    return $this->addCommand(
        $type, compact('index', 'columns', 'algorithm')
    );
}

Which explicitly says that:

If no name was specified for this index, we will create one using basic convention of the table name, followed by the columns, followed by an index type, such as primary or index, which makes the index unique.

IN SUMMARY

You must create your unique index like:

$table->primary(['id_alumno','id_curso_escolar','id_curso_asignatura'],'asignaturas_alumno_pk');

Where asignaturas_alumno_pk is a name that I put and you can change it to whatever you like.