sql - transacciones - ¿Cómo crear una clave primaria compuesta utilizando Migración en Yii2?

yii2 query builder

Question

Intenté ejecutar yii migrate , pero mostró el siguiente 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

Aquí está mi código:

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;
}
}

Entonces, ¿cómo crear una clave primaria compuesta utilizando Migración en Yii2?

Answer 1

Lo ideal es crear unión para dos tablas. Puede usar la línea de comandos Yii para crear la migración con solo un código de línea:

php yii migrate/create create_junction_table_for_post_and_tag_tables --fields="created_at:bigInteger"

Se genera:

/**
* Handles the creation for table `post_tag`.
* Has foreign keys to the tables:
*
* - `post`
* - `tag`
*/
class m160328_041642_create_junction_table_for_post_and_tag_tables extends Migration
{
/**
* @inheritdoc
*/
public function up()
{
$this->createTable(''post_tag'', [
''post_id'' => $this->integer(),
''tag_id'' => $this->integer(),
''created_at'' => $this->dateTime(),
''PRIMARY KEY(post_id, tag_id)'',
]);
// creates index for column `post_id`
$this->createIndex(
''idx-post_tag-post_id'',
''post_tag'',
''post_id''
);
// add foreign key for table `post`
$this->addForeignKey(
''fk-post_tag-post_id'',
''post_tag'',
''post_id'',
''post'',
''id'',
''CASCADE''
);
// creates index for column `tag_id`
$this->createIndex(
''idx-post_tag-tag_id'',
''post_tag'',
''tag_id''
);
// add foreign key for table `tag`
$this->addForeignKey(
''fk-post_tag-tag_id'',
''post_tag'',
''tag_id'',
''tag'',
''id'',
''CASCADE''
);
}
/**
* @inheritdoc
*/
public function down()
{
// drops foreign key for table `post`
$this->dropForeignKey(
''fk-post_tag-post_id'',
''post_tag''
);
// drops index for column `post_id`
$this->dropIndex(
''idx-post_tag-post_id'',
''post_tag''
);
// drops foreign key for table `tag`
$this->dropForeignKey(
''fk-post_tag-tag_id'',
''post_tag''
);
// drops index for column `tag_id`
$this->dropIndex(
''idx-post_tag-tag_id'',
''post_tag''
);
$this->dropTable(''post_tag'');
}
}

Y la estructura de la mesa sería así:

+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| post_id | int(11) | NO | PRI | NULL | |
| tag_id | int(11) | NO | PRI | NULL | |
| created_at | bigint(20) | YES | | NULL | |
+------------+------------+------+-----+---------+-------+

Por favor revise la siguiente URL mencionada para referencia:

http://www.yiiframework.com/doc-2.0/guide-db-migrations.html

Answer 2

prueba de esta manera

public function safeUp()
{
$this->createTable(''news-cate'', [
''news-id'' =>''int NOT NULL'',
''cate-id'' =>''int NOT NULL'',
''PRIMARY KEY (news-id,cate-id)''
]);
$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");
}

Answer 3

ACTUALIZAR

A partir de la versión 2.06 puede usar el nuevo generador de esquemas:

<?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'']);
...
?>

RESPUESTA ORIGINAL

No agregue claves primarias en la creación de tablas, solo declare tipos:

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

Después de eso, puede agregar la clave principal compuesta de esta manera:

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

Para varias columnas, la matriz está permitida en el método addPrimaryKey() .

Esto es mejor que escribir sql crudo.