CDbException: CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 2292 OCIStmtExecute: ORA-02292: integrity constraint (QUIZMO_DEV.FK_ANSWERS_QUESTIONS) violated – child record found
(/web/quizmo/PDO_OCI-1.0/oci_statement.c:142). The SQL statement executed was: DELETE FROM “QUESTIONS”
Totally annoying. Now I’m not one who has a ton of experience with foreign keys as most places seem to have relations with tables but never explicitly link them as such.
So a FK just doesn’t let you add a record when the record it’s linking to doesn’t exist and won’t let you delete a record when there is some other record linking to it.
The problem is I don’t think Yii thought about these when they did fixtures. They built in a way to deal with it, but it’s a little annoying.
Let’s ignore the fact that all of my tables are in caps because I want this to work in Oracle without having to throw single quotes around everything all the time. Let’s take a look at the fixtures of a section of my tables in a project I’m working on right now.
fixtures/
USERS.php
COLLECTIONS.php
USERS_COLLECTIONS.php
QUIZZES.php
QUESTIONS.php
ANSWERS.php
First I need an init.php in the fixture directory. This is called from /yii/framework/test/CDbFixtureManager.php
public function prepare()
{
$initFile=$this->basePath . DIRECTORY_SEPARATOR . $this->initScript;
$this->checkIntegrity(false);
if(is_file($initFile))
require($initFile);
else
{
foreach($this->getFixtures() as $tableName=>$fixturePath)
{
$this->resetTable($tableName);
$this->loadFixture($tableName);
}
}
$this->checkIntegrity(true);
}
So the init file just needs to do this:
foreach($this->getFixtures() as $tableName=>$fixturePath)
{
$this->resetTable($tableName);
$this->loadFixture($tableName);
}
The issue is the resets and loads need to be done in the right order. So the way I went about this is I put in an array of the tables, using that and a reversed version of the array.
$reset_order = array(
'USERS_COLLECTIONS',
'ANSWERS',
'QUESTIONS',
'QUIZES',
'USERS',
'COLLECTIONS',
);
$load_order = array_reverse($reset_order);
foreach($this->getFixtures() as $tableName=>$fixturePath){
if(!in_array($tableName, $reset_order)){
throw new CException("Table '$tableName' is not in the reset_order.");
}
if(!in_array($tableName, $load_order)){
throw new CException("Table '$tableName' is not in the load_order.");
}
}
foreach($reset_order as $tableName){
//echo("resetting $tableName\n");
// this runs the TABLE.init.php if it exists
// otherwise it just does a $this->truncateTable($tableName);
$this->resetTable($tableName);
}
foreach($load_order as $tableName){
//echo("loading $tableName\n");
$this->loadFixture($tableName);
}
That’s not all though, because EVERY table needs a TABLE.init.php file that will be run when “resetTable()” is run in the above script.
fixtures/
init.php
USERS.php
USERS.init.php
COLLECTIONS.php
COLLECTIONS.init.php
USERS_COLLECTIONS.php
USERS_COLLECTIONS.init.php
QUIZZES.php
QUIZZES.init.php
QUESTIONS.php
QUESTIONS.init.php
ANSWERS.php
ANSWERS.init.php
Without these scripts, each table will just DELETE FROM MYTABLE when resetTable is called. The problem with this is if you DELETE FROM COLLECTIONS without deleting the QUIZZES first, you get an integrity constraint violation. So a table init file needs to truncate all tables that are “children” of that table in the order of smallest to largest. COLLECTIONS.init.php looks like this:
truncateTable('USERS_COLLECTIONS');
$this->truncateTable('ANSWERS');
$this->truncateTable('QUESTIONS');
$this->truncateTable('QUIZES');
$this->truncateTable('COLLECTIONS');
?>