CakePHP3 How to stop auto increment on auto generated id column

Table of Contents

I researched how to stop auto increment of auto generated id column in CakePHP3.

Checked Environment

I researched on MySQL.

CakePHP 3.0.9 (Phinx 0.4.4, MySQL 5.6.19)
PHP 5.5.9

The command php bin/cake.php bake migration CreateXXXXXXX ... generate a migration code. After that, php bin/cake.php migraitons migrate creates tables in database with auto incremental id columns. How can we stop auto increment on auto generated id column?

migration code to create not auto incremental id column

The answer is below.

<?php

use PhinxMigrationAbstractMigration;

class CreateSomeThings extends AbstractMigration
{
  public function change()
  {
    $table = $this->table('some_things', ['id' => false, 'primary_key' => 'id']);
    $table->addColumn('id', 'integer');
    $table->addColumn('name', 'string', [
      'defalt' => null,
      'limit' => 255,
      'null' => false,
    ]);
    $table->create();
  }
}

<?php

use PhinxMigrationAbstractMigration;

class CreateSomeThings extends AbstractMigration

{

public function change()

{

$table = $this->table('some_things', ['id' => false, 'primary_key' => 'id']);

$table->addColumn('id', 'integer');

$table->addColumn('name', 'string', [

'defalt' => null,

'limit' => 255,

'null' => false,

]);

$table->create();

}

'id' => false prevent from auto generation of id column. And 'primary_key' => 'id' sets primary key columns. Id column should be registered with filename

I researched about the way to turn off only AUTO_INCREMENT and not to turn off id column auto generation.

The code to generate id column

According to my research, we can’t turn off only AUTO_INCREMENT. vendor/robmorgan/phinx/src/Phinx/Db/Adapter/MysqlAdapter.php explains.

The following code is 210-230 line of MysqlAdapter.php.

        // Add the default primary key
        $columns = $table->getPendingColumns();
        if (!isset($options['id']) || (isset($options['id']) && $options['id'] === true)) {
            $column = new Column();
            $column->setName('id')
                   ->setType('integer')
                   ->setIdentity(true);

            array_unshift($columns, $column);
            $options['primary_key'] = 'id';

        } elseif (isset($options['id']) && is_string($options['id'])) {
            // Handle id => "field_name" to support AUTO_INCREMENT
            $column = new Column();
            $column->setName($options['id'])
                   ->setType('integer')
                   ->setIdentity(true);

            array_unshift($columns, $column);
            $options['primary_key'] = $options['id'];
        }

// Add the default primary key

$columns = $table->getPendingColumns();

if (!isset($options['id']) || (isset($options['id']) && $options['id'] === true)) {

$column = new Column();

$column->setName('id')

->setType('integer')

->setIdentity(true);

array_unshift($columns, $column);

$options['primary_key'] = 'id';

} elseif (isset($options['id']) && is_string($options['id'])) {

// Handle id => "field_name" to support AUTO_INCREMENT

$column = new Column();

$column->setName($options['id'])

->setType('integer')

->setIdentity(true);

array_unshift($columns, $column);

$options['primary_key'] = $options['id'];

}

That’s the code to generate id column. In that case, id column is created as integer. And setIdentity(true) make the column auto-incremental, it’s not controlled by parameter. $options['primary_key'] = 'id' equals to 'primary_key' => 'id', as we added above.

When we do not create id column, condition of if and elseif should be false. Our manually added code, 'id' => false, meets this.

The following code is line 992-1020 of the same file, vendor/robmorgan/phinx/src/Phinx/Db/Adapter/MysqlAdapter.php. It reveals that the column will be auto incremental when setIdentity(true) is executed.

    protected function getColumnSqlDefinition(Column $column)
    {
        $sqlType = $this->getSqlType($column->getType(), $column->getLimit());

        $def = '';
        $def .= strtoupper($sqlType['name']);
        if ($column->getPrecision() && $column->getScale()) {
            $def .= '(' . $column->getPrecision() . ',' . $column->getScale() . ')';
        } elseif (isset($sqlType['limit'])) {
            $def .= '(' . $sqlType['limit'] . ')';
        }
        if (($values = $column->getValues()) && is_array($values)) {
            $def .= "('" . implode("', '", $values) . "')";
        }
        $def .= (!$column->isSigned() && isset($this->signedColumnTypes[$column->getType()])) ? ' unsigned' : '' ;
        $def .= ($column->isNull() == false) ? ' NOT NULL' : ' NULL';
        $def .= ($column->isIdentity()) ? ' AUTO_INCREMENT' : '';
        $def .= $this->getDefaultValueDefinition($column->getDefault());

        if ($column->getComment()) {
            $def .= ' COMMENT ' . $this->getConnection()->quote($column->getComment());
        }

        if ($column->getUpdate()) {
            $def .= ' ON UPDATE ' . $column->getUpdate();
        }

        return $def;
    }

protected function getColumnSqlDefinition(Column $column)

{

$sqlType = $this->getSqlType($column->getType(), $column->getLimit());

$def = '';

$def .= strtoupper($sqlType['name']);

if ($column->getPrecision() && $column->getScale()) {

$def .= '(' . $column->getPrecision() . ',' . $column->getScale() . ')';

} elseif (isset($sqlType['limit'])) {

$def .= '(' . $sqlType['limit'] . ')';

}

if (($values = $column->getValues()) && is_array($values)) {

$def .= "('" . implode("', '", $values) . "')";

}

$def .= (!$column->isSigned() && isset($this->signedColumnTypes[$column->getType()])) ? ' unsigned' : '' ;

$def .= ($column->isNull() == false) ? ' NOT NULL' : ' NULL';

$def .= ($column->isIdentity()) ? ' AUTO_INCREMENT' : '';

$def .= $this->getDefaultValueDefinition($column->getDefault());

if ($column->getComment()) {

$def .= ' COMMENT ' . $this->getConnection()->quote($column->getComment());

}

if ($column->getUpdate()) {

$def .= ' ON UPDATE ' . $column->getUpdate();

}

return $def;

}

From the above, it is clear that id column should be auto-incremental column when it’s created automatically. In other words, you should stop auto-creating of id column to prevent auto increment.

How is the option used?

To prevent auto-increment, 'id' => false is required. Now, how is the option 'primary_key' => 'id' used?

The following code is line 258-280 of MysqlAdapter.php. Here, prepare for setting primary key according to option parameter.

        // set the primary key(s)
        if (isset($options['primary_key'])) {
            $sql = rtrim($sql);
            $sql .= ' PRIMARY KEY (';
            if (is_string($options['primary_key'])) {       // handle primary_key => 'id'
                $sql .= $this->quoteColumnName($options['primary_key']);
            } elseif (is_array($options['primary_key'])) { // handle primary_key => array('tag_id', 'resource_id')
                // PHP 5.4 will allow access of $this, so we can call quoteColumnName() directly in the
                // anonymous function, but for now just hard-code the adapter quotes
                $sql .= implode(
                    ',',
                    array_map(
                        function ($v) {
                            return '`' . $v . '`';
                        },
                        $options['primary_key']
                    )
                );
            }
            $sql .= ')';
        } else {
            $sql = substr(rtrim($sql), 0, -1);              // no primary keys
        }

// set the primary key(s)

if (isset($options['primary_key'])) {

$sql = rtrim($sql);

$sql .= ' PRIMARY KEY (';

if (is_string($options['primary_key'])) { // handle primary_key => 'id'

$sql .= $this->quoteColumnName($options['primary_key']);

} elseif (is_array($options['primary_key'])) { // handle primary_key => array('tag_id', 'resource_id')

// PHP 5.4 will allow access of $this, so we can call quoteColumnName() directly in the

// anonymous function, but for now just hard-code the adapter quotes

$sql .= implode(

',',

array_map(

function ($v) {

return '`' . $v . '`';

$options['primary_key']

)

);

}

$sql .= ')';

} else {

$sql = substr(rtrim($sql), 0, -1); // no primary keys

}

Required Condition

Thank you for reading my article.

From the above, it is clear that option setting, ['id' => false, 'primary_key' => 'id'], and adding id column by addColumn are required. Without 'id' => false, auto incremental id column will be created. Without 'primary_key' => 'id', the id column can’t be the primary key. Of course, using addIndex is ok if you want only indexing, not primary key.