Unsigned primary keys

[johnblythe]

Is there a way to cause the autogen'd primary key ('id') to be unsigned?

[twoixter]

I think not really, because the autogen'd column type is hardcoded right in the Db\Adapter::createTable method. However, what I'm doing is changing the column type after the main table is created.

I'm sure you've tried some of this, I post here my method if it is of some help.

Method 1) Preferred, since I think it will be compatible with all DB adapters. Right after creating the table, just modify the id column using a column class:

public function up()
{
    [... create the table ...]

    $col_id = new Phinx\Db\Table\Column();
    $col_id->setIdentity(true);  // Needed to be AUTO_INCREMENT
    $col_id->setType("integer");  // Or it can also be "biginteger"
    $col_id->setOptions(["signed" => false]);  // Makes the column unsigned
    $my_table->changeColumn("id", $col_id)->save();
}

Method 2) The obvious ALTER TABLE. Here the syntax is for MySQL only

public function up()
{
    [... create the table ...]

    $this->execute("ALTER TABLE `my_table` CHANGE `id` `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT");
}

[johnblythe]

Thanks! I had ended up going with the second option, the ALTER route. I wish I could just use the the packaged methods, but I've found that I end up spending far too much time toying, testing, and tweaking as opposed to just running an execute() command and being done with it.

Thanks again!

[robmorgan]

thx @twoixter . I'd recommend to use either of those methods for now.

[adayth]

Alternative method overriding Phinx classes.
https://gist.github.com/adayth/f0ead7bd210e3968208e
Edit: wrong link

[paul-at-cybr]

This is a feature I miss. Doesn't necessarily break the app, but it hurts inside knowing that all my auto-increment id's will be signed for no practical reason.

[steefaan]

@robmorgan I would love to see this feature. Sure @twoixter solution works great (thanks!) but it would be nicer to have the possibility to configure it directly especially in such a nice library like yours :)

[caseycs]

Just posted a small update for @twoixter solution: https://gist.github.com/adayth/f0ead7bd210e3968208e#gistcomment-1585602

[bubach]

Who came up with the brilliant idea to set primary keys as signed in the first place, that's one of the reasons I'm starting to think raw SQL files for migrations wasn't such a bad idea after all... Overall Phinx feeling -> This is just retarded.

[robmorgan]

Hi @bubach that was me and it's why I no longer work on this project anymore. You might want to check out this instead: https://flywaydb.org/ 👍