Unable to add an AUTO_INCREMENTING primary key to a populated table that doesn't have one

[swapnik1]

$tag_data = $this->table('tag_data');
$tag_data
->addColumn(
	'td_id',
	'integer',
	[
		'null' => false,
		'signed' => false,
		'after' => 'tag_id'
	]
)
->changePrimaryKey('td_id')
->save();

This doesn't work because it is creating the td_id column and initializing all rows to 0.

[MasterOdin]

As you note this is not supported in phinx currently, and your current option would be to do a direct $this->execute("ALTER TABLE tag_data DROP PRIMARY KEY, ADD COLUMN td_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY AFTER tag_id").

---

If going through phinx, you need to use the identity option to make a column an AUTO_INCREMENT:

->addColumn(
	'td_id',
	'integer',
	[
		'null' => false,
		'signed' => false,
		'after' => 'tag_id',
                'identity' => true
	]
)

Though this will throw the following error:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key

which is expected from phinx as it attempts to do the following (assuming existing primary key):

ALTER TABLE `test_table` ADD `td_id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
ALTER TABLE `test_table` DROP PRIMARY KEY, ADD PRIMARY KEY (`td_id`);

The way for this to work would be to do the following:

ALTER TABLE `test_table` DROP PRIMARY KEY, ADD `td_id` INT(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY;

While the above use-case should probably be supported, I'm not sure there's currently a good way to do this as minimally it involves having some way to append the PRIMARY KEY directive on AddColumn action, and that we need plan introspection to rewrite the ChangePrimaryKey action to the above. I would suggest tackling this in two steps:

1. Add a primary_key option to the Column options, allowing the following:

->addColumn(
	'td_id',
	'integer',
	[
		'null' => false,
		'signed' => false,
		'after' => 'tag_id',
                'identity' => true,
                'primary_key' => true
	]
)

where then this would append PRIMARY KEY to the column definition generated in getColumnSqlDefinition.

2. Add the plan introspection to handle combining the AddColumn and ChangePrimaryKey actions if appropriate, which would be defined as something like loop through actions and see if ChangePrimaryKey action exists and if so, loop through actions to see if there is an AddColumn that matches the column, and if so, alter column definition in above to have 'primary_key' => true and prepend a DELETE PRIMARY KEY to the alter.