Hello All,
I migrated create_issue_user_and_assignment_tables successful, but want to roll back and type ./yiic migrate down.
It failed.
code fyi.
migration file:
<?php
class m130302_100145_create_issue_user_and_assignment_tables extends CDbMigration
{
/*public function up()
{
}
public function down()
{
echo "m130302_100145_create_issue_user_and_assignment_tables does not support migration down.\n";
return false;
}*/
// Use safeUp/safeDown to do migration with transaction
public function safeUp()
{
//create the issue table
$this->createTable('tbl_issue', array(
'id' => 'pk',
'name' => 'string NOT NULL',
'description' => 'text',
'project_id' => 'int(11) DEFAULT NULL',
'type_id' => 'int(11) DEFAULT NULL',
'status_id' => 'int(11) DEFAULT NULL',
'owner_id' => 'int(11) DEFAULT NULL',
'requester_id' => 'int(11) DEFAULT NULL',
'create_time' => 'datetime DEFAULT NULL',
'create_user_id' => 'int(11) DEFAULT NULL',
'update_time' => 'datetime DEFAULT NULL',
'update_user_id' => 'int(11) DEFAULT NULL',
), 'ENGINE=InnoDB');
//create the user table
$this->createTable('tbl_user', array(
'id' => 'pk',
'username' => 'string NOT NULL',
'email' => 'string NOT NULL',
'password' => 'string NOT NULL',
'last_login_time' => 'datetime DEFAULT NULL',
'create_time' => 'datetime DEFAULT NULL',
'create_user_id' => 'int(11) DEFAULT NULL',
'update_time' => 'datetime DEFAULT NULL',
'update_user_id' => 'int(11) DEFAULT NULL',
), 'ENGINE = InnoDB');
//create the assignment table that allows for many-to-many
//relationship between projects and users
$this->createTable('tbl_project_user_assignment', array(
'project_id' => 'int(11) NOT NULL',
'user_id' => 'int(11) NOT NULL',
'PRIMARY KEY(`project_id`,`user_id`)',
), 'ENGINE=InnoDB');
//foreign key relationships
//the tbl_issue.project_id is a reference to tbl_project.id
$this->addForeignKey("fk_issue_project", "tbl_issue", "project_id", "tbl_project", "id", "CASCADE", "RESTRICT");
//the tbl_issue.owner_id is a reference to tbl_user.id
$this->addForeignKey("fk_issue_owner", "tbl_issue", "owner_id", "tbl_user", "id", "CASCADE", "RESTRICT");
//the tbl_issue.requester_id is a reference to tbl_user.id
$this->addForeignKey("fk_issue_requester", "tbl_issue", "requester_id", "tbl_user", "id", "CASCADE", "RESTRICT");
//the tbl_project_user_assignment.project_id is a reference to tbl_project.id
$this->addForeignKey("fk_project_user", "tbl_project_user_assignment", "project_id", "tbl_project", "id", "CASCADE", "RESTRICT");
//the tbl_project_user_assignment.user_id is a reference to tbl_user.id
$this->addForeignKey("fk_user_project", "tbl_project_user_assignment", "user_id", "tbl_user", "id", "CASCADE", "RESTRICT");
}
public function safeDown()
{
$this->truncateTable('tbl_project_user_assignment');
$this->truncateTable('tbl_issue');
$this->truncateTable('tbl_user');
$this->dropTable('tbl_project_user_assignment');
$this->dropTable('tbl_issue');
$this->dropTable('tbl_user');
}
}
result:
dami@ubuntu:/var/www/trackstar2/protected$ ./yiic migrate down
Yii Migration Tool v1.0 (based on Yii v1.1.13-dev)
Total 1 migration to be reverted:
m130302_100145_create_issue_user_and_assignment_tables
Revert the above migration? (yes|no) [no]:yes
*** reverting m130302_100145_create_issue_user_and_assignment_tables
> truncate table tbl_project_user_assignment ... done (time: 0.108s)
> truncate table tbl_issue ... done (time: 0.100s)
> truncate table tbl_user ...Exception: CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (`trackstar2`.`tbl_issue`, CONSTRAINT `fk_issue_requester` FOREIGN KEY (`requester_id`) REFERENCES `trackstar2`.`tbl_user` (`id`)). The SQL statement executed was: TRUNCATE TABLE `tbl_user` (/var/www/htdocs/yii/framework/db/CDbCommand.php:357)
#0 /var/www/htdocs/yii/framework/db/CDbCommand.php(1356): CDbCommand->execute()
#1 /var/www/htdocs/yii/framework/db/CDbMigration.php(265): CDbCommand->truncateTable('tbl_user')
#2 /var/www/trackstar2/protected/migrations/m130302_100145_create_issue_user_and_assignment_tables.php(79): CDbMigration->truncateTable('tbl_user')
#3 /var/www/htdocs/yii/framework/db/CDbMigration.php(77): m130302_100145_create_issue_user_and_assignment_tables->safeDown()
#4 /var/www/htdocs/yii/framework/cli/commands/MigrateCommand.php(410): CDbMigration->down()
#5 /var/www/htdocs/yii/framework/cli/commands/MigrateCommand.php(145): MigrateCommand->migrateDown('m130302_100145_...')
#6 [internal function]: MigrateCommand->actionDown(Array)
#7 /var/www/htdocs/yii/framework/console/CConsoleCommand.php(172): ReflectionMethod->invokeArgs(Object(MigrateCommand), Array)
#8 /var/www/htdocs/yii/framework/console/CConsoleCommandRunner.php(67): CConsoleCommand->run(Array)
#9 /var/www/htdocs/yii/framework/console/CConsoleApplication.php(91): CConsoleCommandRunner->run(Array)
#10 /var/www/htdocs/yii/framework/base/CApplication.php(169): CConsoleApplication->processRequest()
#11 /var/www/htdocs/yii/framework/yiic.php(33): CApplication->run()
#12 /var/www/trackstar2/protected/yiic.php(7): require_once('/var/www/htdocs...')
#13 /var/www/trackstar2/protected/yiic(4): require_once('/var/www/tracks...')
#14 {main}
*** failed to revert m130302_100145_create_issue_user_and_assignment_tables (time: 0.225s)
Migration failed. All later migrations are canceled.
pls help how to proceed and thx a lot 