I have a table that contains a primary key and two foreign keys (i.e.
id, A_id, B_id, all integers) as well as other data (this is not a join
table). My data design is such that the combination A_id and B_id must
be unique. I'm considering two ways of doing this:
1) Before save query the DB and find the primary key (id) for a record
that has A_id and B_id. If that record exists set the id field in my
$data and then Model->save(). This will overwrite the old record if it
exists and create a new one if it doesn't.
2) Change id from an auto increment id to a text field. Always set the
id to be the composite (A_id . '/' . B_id). This will also overwrite
the old record if it exists and create a new one if it doesn't.
Any suggestions on which one I should implement or is there another
option?
I am enforcing a unique composite key in a couple of my projects.
The primary key is still the id field which is set to autoincrement, so modifying a record works exactly the same as a regular cake edit.
When adding a record MySQL will throw an error which is uncaught by the cake model, but the save will fail. Currently this is how I am dealing with the insert failure in my controller. I am working on something more robust to put in my model.
//check for mysql errors like unique failures, my model is Guideline in this scenario
$connection =& ConnectionManager::getInstance();
$source =& $connection->getDataSource($this->Guideline->useDbConfig);
$db_error = $source->lastError();
if ( strstr($db_error, 'Duplicate') ) { //mysql error message contains this string
$this->Guideline->invalidate('row'); //this is one of my "unique" composite keys
//I add this error message onto the errortag in the view
$this->set('duplicateError', 'This guideline already exists in your matrix.');
}
$connection =& ConnectionManager::getInstance();
$source =& $connection->getDataSource($this->Guideline->useDbConfig);
$db_error = $source->lastError();
if ( strstr($db_error, 'Duplicate') ) { //mysql error message contains this string
$this->Guideline->invalidate('row'); //this is one of my "unique" composite keys
//I add this error message onto the errortag in the view
$this->set('duplicateError', 'This guideline already exists in your matrix.');
}
So far everything is workigng great and I have had no problems.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake PHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/cake-php
-~----------~----~----~----~------~----~------~--~---
