How to create a non standard primary key in migration?

Yii 2.0
1

I am trying to “convert” SQL code from guide’s example into migration. The SQL code assumes non-standard primary key (i.e. char(2) not null instead of int):

CREATE TABLE `country` (
  `code` CHAR(2) NOT NULL PRIMARY KEY,
  `name` CHAR(52) NOT NULL,
  `population` INT(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When I am trying to “replicate” the above with migration, the following code works:

$this->createTable('{{%country}}', [
    'code' => $this->primaryKey(),
    'name' => $this->char(52)->notNull(),
    'population' => $this->integer(11)->notNull()->defaultValue(0),
]);

But this one:

$this->createTable('{{%country}}', [
    'code' => $this->primaryKey()->char(2)->notNull(),
    'name' => $this->char(52)->notNull(),
    'population' => $this->integer(11)->notNull()->defaultValue(0),
]);

fails with “Exception: Calling unknown method: yii\db\mysql\ColumnSchemaBuilder::char()” error.

What am I missing? Why 'code' => $this->primaryKey()->char(2)->notNull() line does not work? How to create a non-standard primary key in migration?

2

The error is pretty saying it all
Try:

$this->createTable('{{%country}}', [
    'code' => $this->primaryKey(2)->notNull(),
    'name' => $this->char(52)->notNull(),
    'population' => $this->integer(11)->notNull()->defaultValue(0),
]);
3

Unfortunately for me this error says pretty much nothing.

And your example code is only syntactically correct (meaning that such migrations works). But, it is semantically incorrect (meaning that the achieved result is other than expected).

Your code only differs from my code in that it creates int(2) primary key. It does not create a char(2) primary key – which is what I am trying to achieve.

4

This has worked for me:

$this->createTable('{{%country}}', [
    'code' => $this->char(2)->notNull(),
    'name' => $this->char(52)->notNull(),
    'population' => $this->integer(11)->notNull()->defaultValue(0),
]);

$this->addPrimaryKey('pk_on_code', '{{%country}}', 'code');

Though I still cannot understand why calling this in first place (with $this->primaryKey()->char(2)->notNull()) does not work.

5

I assume,
Creates integer column with specified length. It’s one of the shortcut for common type of PK column. You can use other methods for custom pk like you did already.

6

You are right. The main question (how to create custom primary key) is solved.

But, since both $this->primaryKey() and $this->char() (and many more) are part of the same class (yii\db\ColumnSchemaBuilder) and both (all of them) return yii\db\ColumnSchemaBuilder then I don’t understand why I can chain $this->char() and other together, but I can’t chain $this->primaryKey() in the very same way.

7

BTW, https://github.com/bizley/yii2-migration from @Bizley do a great job.

1 Like