Doctrine 2. orm:schema-tool:update . Set start id

Question

When I use ./bin/doctrine orm:fixtures:load to populate tables with sample data first migration sets auto incremental table id like 1,2,3,4,5 etc...

After second orm:fixtures:load migration command it purges all data and sets ids like 5,6,7,8,9 and so on...

How can I reset AI id counter to 1 when I load fixtures many times?

Answer 1

$ app/console help doctrine:fixtures:load

By default Doctrine Data Fixtures uses DELETE statements to drop the existing rows from the database. If you want to use a TRUNCATE statement instead you can use the --purge-with-truncate flag:

./app/console doctrine:fixtures:load --purge-with-truncate

Truncate will reset the auto increments.

UPDATE

The console command is for Symfony, but it should be the same using Doctrine only:

./bin/doctrine orm:fixtures:load --purge-with-truncate

UPDATE #2 for the comment about throwing an exception

If you have foreign keys, you can only reset the AUTO_INCREMENT through regular SQL:

$connection = $this->getEntityManager()->getConnection();
$connection->exec("ALTER TABLE <tablename> AUTO_INCREMENT = 1;");

Answer 2

In Zend 2 I have used below mentioned code to truncate and reset auto incremental value to 1. Use this code in your repository.

// To delete all records
$queryBuilder = $this->createQueryBuilder('c');
$queryBuilder->delete();
$queryBuilder->getQuery()->execute();

//Reset table auto increment.
$tableName = $this->getClassMetadata()->getTableName();
$connection = $this->getEntityManager()->getConnection();
$connection->exec("ALTER TABLE " . $tableName . " AUTO_INCREMENT = 1;");