Using PHP Objects to Access Your Database Tables (Part 2)
By Tony Marston2005-04-07
Changing Candidate Keys
|
|
Although it is not normal procedure to change the value of a primary key - indeed most database management systems actually forbid it - it is quite allowed to change the value of a candidate key. The only rule is that the value you wish to change to is not already in use. You also have to bear in mind that a table can have more than one candidate key, and that each key can contain more than one field. Using the $unique_keys array described previously it is possible to completely automate this validation procedure using code similar to the following:
// validate any optional unique/candidate keysYou will have noticed here that in order to check that a candidate key value has changed I have had to retrieve the current record from the database just before it is updated. It is then a straightforward process to compare the original values with the user-supplied values to see what has changed.
if (!empty($this->unique_keys)) {
// there may be several keys with several fields in each
foreach ($this->unique_keys as $key) {
$where1 = NULL; // for original values
$where2 = NULL; // for changed values
foreach ($key as $fieldname) {
if (empty($where1)) {
$where1 = "$fieldname='" .$this->data_original[$fieldname] ."'";
$where2 = "$fieldname='" .$updatearray[$fieldname] ."'";
} else {
$where1 .= " AND $fieldname='" .$this->data_original[$fieldname] ."'";
$where2 .= " AND $fieldname='" .$updatearray[$fieldname] ."'";
} // if
} // foreach
if ($where1 <> $where2) {
// key has changed, so check for uniqueness
$query = "SELECT count(*) FROM $this->tablename WHERE $where2";
$count = $this->getCount($query);
if ($count <> 0) {
// set error message for each field within this key
foreach ($key as $fieldname) {
$this->errors[$fieldname] = "A record already exists with this key.";
} // foreach
return $fieldarray;
} // if
} // if
} // foreach
} // if
Tutorial Pages:
» Intended Audience
» Extending the MySQL SELECT statement
» Adding the features of a Data Dictionary/Repository
» Using the Data Dictionary/Repository
» Changing Candidate Keys
» Deleting Rows
» Custom Processing
» Virtual Database Tables
» Summary
|
