How to alter the auto_increment of a mysql-table using Doctrine

Question

Hi,

the question says it all it think :-) What is the best way to execute a query such as:

ALTER TABLE tablename AUTO_INCREMENT = 1;

using Doctrine?

Answer 1

Are you trying to manipulate the auto-increment value of your table? If you want to do this, you COULD do

SET @@auto_increment_increment=10;

Keep in mind that this is a system wide variable that will affect ALL tables.

Generally, I would discourage you to manipulate the way that the primary key (I just assume thsi) is generated.

If you need to give a custom value, say i * 10, you could either write a trigger/function that does the job or a more easily, you can create a view provides you with the last inserted id (say 150) and adds your value (e.g. 10) to it so you can get 160.

Doctrine itself does not support this (as far as I know) and that's good in my opinion. If you need to set different increments to the primary keys, PostgreSWL and Oracale for example are able to do so since they use sequences for the generation of the keys that can be manipulated by the dev

Answer 2

You'll have to just execute the query as normal from within the PHP I'm afraid, as this is not something that Doctrine supports.

Note that if you run ALTER TABLE tablename AUTO_INCREMENT = 1; then the next value will be the highest currently in the table + 1. You aren't explicitly setting the value to 1 if there are already records in the table.