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

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”.

About this issue

Original URL
State: closed
Created 8 years ago
Reactions: 4
Comments: 21 (16 by maintainers)

Commits related to this issue

Add migration file example for DbSession Creating a `char` primaryKey in a migration is non-obvious. Used solution from this [issue comment](https://github.com/yiisoft/yii2/issues/10889#issuecomment-... — committed to bakavic/yii2 by bakavic 7 years ago
Add migration file example for DbSession (#14227) [skip ci] Creating a `char` primaryKey in a migration is non-obvious. Used solution from this [issue comment](https://github.com/yiisoft/yii2/issues/... — committed to yiisoft/yii2 by bakavic 7 years ago
update (#1) * Fixes #13945: Removed Courier New from error page fonts list since it looks bad on Linux (#13947) * Fixed phpdoc broken links to PHP manual pages [skip ci] * Fixes #10675: Added d... — committed to mixartemev/yii2 by mixartemev 7 years ago

Most upvoted comments

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

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

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

MysteryDragon on Nov 9, 2016

@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');

vercotux on May 22, 2017

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]])
]

mdmunir on Feb 21, 2016

Then how to create pk without Auto Increment

pptyasar on Nov 9, 2016