Me again, will probably find the answer shortly after posting again.
Trying to figure out how to create a primary key consisting of two columns, as per the Agile Web app book. I have tried:
$this->createIndex('PRIMARY', 'tbl_project_user_assignment', 'project_id, user_id', 'true');
but that returns:
*** applying m110828_152005_create_project_user_assignment_table
> create table tbl_project_user_assignment ... done (time: 0.019s)
> create unique index PRIMARY on tbl_project_user_assignment (project_id, user_id) ...Exception: CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1280 Incorrect index name 'PRIMARY'. The SQL statement executed was: CREATE UNIQUE INDEX `PRIMARY` ON `tbl_project_user_assignment` (`project_id`, `user_id`)
...
which is obviously wrong.
If I’m adding the primary key after creating the table it needs to be something like:
ALTER TABLE `trackstar`.`tbl_project_user_assignment` ADD PRIMARY KEY ( `project_id` , `user_id` )
which I don’t seem to be able to do. I tried:
public function safeUp()
{
$this->createTable('tbl_project_user_assignment', array(
'project_id' => 'pk',
'user_id' => 'pk',
'create_time' => 'datetime',
'create_user_id' => 'integer',
'update_time' => 'integer',
'update_user_id' => 'integer',
));
which gave me:
*** applying m110828_152005_create_project_user_assignment_table
> create table tbl_project_user_assignment ...Exception: CDbCommand failed to execute the SQL statement: 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 statement executed was: CREATE TABLE `tbl_project_user_assignment` (...
How do I go about creating a Primary key of two columns? Or do I have to use something like:
$this->execute('ALTER TABLE `trackstar`.`tbl_project_user_assignment` ADD PRIMARY KEY ( `project_id`, `user_id` )');
after creating the table, which breaks the DB agnostic approach of using migrations? I suppose you could check to see what DB engine is used in the configuration and write the necessary raw sql for each possibility.
Thanks!