How to create composite primary key by using Migration in Yii2?

sql yii2 database-migration
user1571234 picture user1571234 · Aug 21, 2015 · Viewed 16.7k times · Source

I tried to run yii migrate, but it showed the following error:

create table news-cate ...Exception: 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
The SQL being executed was: CREATE TABLE `news-cate` (
        `news-id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `cate-id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY

Here is my code:

class m150821_083020_create_newscate_table extends Migration
{
    public function safeUp()
    {
        $this->createTable('news-cate', [
            'news-id' => $this->primaryKey(),
            'cate-id' => $this->primaryKey(),
        ]);
        $this->addForeignKey("fk_news_cate_nid", "news-cate", "news-id", "news", "id", "RESTRICT", "CASCADE");
        $this->addForeignKey("fk_news_cate_cid", "news-cate", "cate-id", "category", "id", "RESTRICT", "CASCADE");
    }

    public function safeDown()
    {
        echo "m150821_083020_create_newscate_table cannot be reverted.\n";
        $this->dropTable("news-cate");
        return false;
    }
}

So how to create composite primary key by using Migration in Yii2?

Answer

arogachev picture arogachev · Aug 21, 2015

UPDATE

As of yii version 2.06 you can use new schema builder:

<?php
use yii\db\Migration;
...
$this->createTable('news-cate', [
    'news-id' => $this->integer()->notNull(),
    'cate-id' => $this->integer()->notNull(),
]);
$this->addPrimaryKey('news-cate_pk', 'news-cate', ['news-id', 'cate-id']);
...
?>

ORIGINAL ANSWER

Don't add primary keys in table creation, only declare types:

use yii\db\Schema;

,,,

$this->createTable('news-cate', [
    'news-id' => Schema::TYPE_INTEGER . ' NOT NULL',
    'cate-id' => Schema::TYPE_INTEGER . ' NOT NULL',
]);

After that you can add the composite primary key like this:

$this->addPrimaryKey('news-cate_pk', 'news-cate', ['news-id', 'cate-id']);

For multiple columns, array is allowed in addPrimaryKey() method.

This is better than writing raw sql.

Related questions

Log the actual SQL query using ActiveRecord with Yii2?

I'm doing this: $students = Student::find()->all(); return $this->render('process', array('students' => $students)); and then this in the view: foreach($students as $student) { echo $student->name . ', &nbsp;'; echo $student->getQuizActivitiesCount(); ?&…

Read More
How to use subquery in the join function of Yii framework 2 ActiveRecord?

Below is my pure SQL query. SELECT a.*, b.* FROM a INNER JOIN b ON a.id = b.a_id INNER JOIN ( SELECT a_id, MAX(add_time) AS max_add_time FROM b GROUP BY a_id ) m ON …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More