Yii2: Migration $this->primaryKey()->unsigned() don't work for MySQL!

[dromok]

Showed error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNSIGNED'".

Because it generated "id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY UNSIGNED" invalid sql.
Need correct sql like this "id int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY".

[MysteryDragon]

+1
Oracle MySQL v5.7.11 / v5.5.47, PHP v7.0.3 / v5.5.9
Temporary solution looks like this:

'id' => $this->integer(11)->unsigned(),
// ... other fields here
'PRIMARY KEY(id)',

Or use addPrimaryKey() method (for example, $this->addPrimaryKey('PRIMARY', 'yourTableName', 'id'))

[samdark]

primaryKey() uses schema pk type which is int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY.

[samdark]

Same as 'id' => 'pk' in the old syntax.

[SilverFire]

So you think we should tag it as wontfix?

[MysteryDragon]

I noticed some mistakes in my solutions:

1. We can't set the AUTO_INCREMENT attribute this ways. Only alterTable or SQL expression can help us here, I guess.
2. "PRIMARY" isn't good name for PRIMARY index. :) It may be blank string or any correct index name (it will be ignored by MySQL) but not the "PRIMARY". Or else we'll receive an MySQL exception.
   So, to my mind it's quite nice to set this attribute in some other way.

[df2]

public function unsignedPrimaryKey();
public function unsignedBigPrimaryKey();

[samdark]

How about charPrimaryKey(), stringPrimaryKey()?

[MysteryDragon]

Maybe ColumnSchemaBuilder::autoIncrement()?
But in this case such method must define some index type.
Or could Yii replace schema parameters, if we extend it by unsigned() methods and so on? This is expected behavior of such methods, I guess.

[df2]

@samdark It makes sense that pk()->unsigned() would produce a primary key as defined by Yii, but an unsigned version.

This is an argument about a virtual capacity. It doesn't _really_ matter that we're throwing away half our "pool of resources" in this case. There is perhaps an OCD affection that drives one nuts tho, when you could access that "half" by the UNSIGNED modifier.

[mdmunir]

Its because the name of primaryKey() itself. Should be canged to another name like serial(), autoIncrement() or something else. Then primaryKey() use for real PRIMARY KEY.

'id' => $this->serial(), // <- int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY

'coa' => $this->char(8)->primaryKey(), // <- char(8) PRIMARY KEY

[
    'order_id' => $this->integer()->notNull(),
    'product_id' => $this->integer()->notNull(),
    ...
    $this->primaryKey(['order_id','product_id']), // <- PRIMARY KEY ([[order_id]], [[product_id]])
]

[cebe]

Its because the name of primaryKey() itself. Should be canged to another name like serial(), autoIncrement() or something else. Then primaryKey() use for real PRIMARY KEY.

:+1:

[df2]

See #10921 it's being fixed. Would you like to add to that @mdmunir . See the changes that have been made so far and how you would like to go ahead based on that.

[pptyasar]

Is this solved?

[MysteryDragon]

@pptyasar , yep. Since 2.0.8.

[pptyasar]

Then how to create pk without Auto Increment

[MysteryDragon]

@pptyasar , currently (<=v2.0.10):

$this->createTable('tableName', [
    'id' => $this->integer()->unsigned()->notNull(),
    // ...
   'PRIMARY KEY ([[id]])',
]);

Or add primary key later, see Migration::addPrimaryKey()

[vercotux]

@MysteryDragon

Or add primary key later, see Migration::addPrimaryKey()

This is how you do it:

$this->createTable('tableName', [
    'id' => $this->integer()->unsigned()->notNull(),
    // ...
]);
$this->addPrimaryKey('pk-id', 'tableName', 'id');