Some kind of constraint issue

[Ninjakreborn]

I really wanted to avoid asking for help, but I had no choice.

I tried everything I can think of and this just isn't working.

I am working on an internal system.  It's basically a CMS for an administrator of a site.

I am building one area of CMS for each table. Projects, Sites, Energy, and Meters.

I finished up projects. I finished it up within a Jquery Jgrid as well as added add/edit/delete functionality.

The system is built using Zend Framework. All of that was no problem at all.

 

Then I went to do the sites. I basically reused the same code since I already had a lot of the stuff for Jgrid done

that allowed me all the ajax add/edit/delete functionality. I hooked up all the backend and went to test it. Ran

into a few issues which I easily fixed. Then ran into a new problem I have never dealt with before. It came back with some error

about a constraint.  The error was something along the lines of:

SQLSTATE[HY000]: General error: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`*****/tbl_site_photos`, CONSTRAINT `site_id` FOREIGN KEY (`site_id`) REFERENCES `tbl_sites` (`site_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)    </p>

That basically was the error I got.  So I did some searching on the error. I have never dealt with or used Constraints before so I

was unfamiliar with the concept. After a few minutes I went through and started setting the constraints to "No Action" because

I thought it would allow me to edit/delete the rows.  We'll it didn't. I did some more reading and found out the constraint

no action doesn't allow you to process an update or a delete.  All I am trying to do is the person goes in and a form comes up

with the data prepopulated into the form for that record.  They edit some fields and submit it back to the database.  It

always returns that error.  Even if I try to submit the form with no fields change...so even without changing any fields it still returns that error.  I was thinking about going through a deleting all the foreign ID's, but I am somewhat familiar with them. I have never had this type of issue. Does anyone have any advice?

[roopurt18]

It would appear that it *thinks* you're trying to update the `site_id` column.  In your UPDATE statement, is `site_id` present?

 

Or, does your update statement look like:

UPDATE `tbl_site_photos` SET `site_id`=$old_value, ... WHERE ...

[Ninjakreborn]

That is the situation with this specific table. The Site_ID is the same as another field called Site_Api_ID. They are suppose to be the same. Site_id is not auto-increment as it is something that can be changed by the user.  They are allowed to change the site_id and when they change that ID it also automatically sets the api_site_id to the site_id as well.

 

I have no choice but to make that field editable.  Since the api id is something that they have to be able to update since it is based off

of what API they are connecting to.  So yes, in this situation it is updating the site_id.

 

Edit: But my query should not be editing anything with the photo table. I am using Zend Framework built in:

			return $table->update($data, $where);		

[Ninjakreborn]

Below is some additional clarification.  First off I have my function which gets the fields from the form:

<?php
	// The grid calls this function when a site is edited
	public function editsitefieldsAction() {
	    // Disable layout
			$this->_helper->layout()->disableLayout();

    		// Setup request and get the needed parameters
			$request                    = $this->getRequest();
    		$site_id                    = $request->getParam('site_id');    

			$fields['site_id']          = $request->getParam['site_id'];
			$fields['site_name']        = $request->getParam('site_name');       
			$fields['site_description'] = $request->getParam['site_description'];
    		$fields['api_url']          = $request->getParam['api_url'];    
      	$fields['api_site_id']      = $request->getParam['site_id'];      
       	$fields['api_username']     = $request->getParam['api_username'];       
			$fields['api_password']     = $request->getParam['api_password'];
    		$fields['api_generation_id']= $request->getParam['api_generation_id'];    
      	$fields['api_usage_id']     = $request->getParam['api_usage_id'];      
       	$fields['api_weather_id']   = $request->getParam['api_weather_id'];       
        $fields['is_active']        = $request->getParam['is_active'];        
        $fields['timezone_offset']  = $request->getParam['timezone_offset'];

			// Now let's database the results
    		$model = $this->_getSitesModel();
			$model->update($fields, $site_id);    
	}
?>

Then I have my model save function which deals with saving the data:

<?php
	public function update(array $data, $id) 
	{
  			// Get table information
			$table  = $this->gettable();

    		// Form where clause
			$where  = $table->getAdapter()->quoteInto('site_id = ?', $id);

    		// Remove fields that we aren't editing
			$fields = $table->info(Zend_Db_Table_Abstract::COLS);    
      	foreach($data as $field => $value) {
					if (!in_array($field, $fields)) {
							unset($data[$field]);
					}
			}

			// Perform edit    
			return $table->update($data, $where);		
	}  

?>

The strange thing..it's also giving me the same error when I am trying to add a new field, which of course uses a different function but I just don't understand how this same issue can affect me adding a new field as well.

 

Edit: Also one other note.  The table I am trying to do this with is sites.  The Sites_photo's should have nothing to do with this table, I don't even know why that error is coming up.

[ignace]

Constraints are used to maintain data integrity which basically means that you can not a delete primary key if there are still foreign keys referring to it which in turn means that before you can delete this record you must delete all records that may contain a foreign key to this field.

 

A good scenario is linux where you can not delete a directory before deleting the files inside and this rule also applies to every directory in the directory.

[Ninjakreborn]

I am not trying to delete the record. I am trying to edit the record. I need to be able to delete as well...but right now I would be happy just getting the editing/adding working for the time being.

[Ninjakreborn]

Out of on error and into another. I went through and did some more reading, as well as based off of what you said. I went ahead and set them all to cascade. Since it was editing the site_id and that was something that was set as a foreign key to 4-5 tables, I had to go through and set them all to cascade.  Once I did that, it started letting me edit. However after editing more than once it returned another error.

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 1

In this situation it's the same thing. It's a simple edit statement (which I showed earlier).

[ignace]

Set your constraints to:

 

ON UPDATE CASCADE ON DELETE CASCADE

 

Or maybe better advice: DROP THE CONSTRAINTS If you don't need the whole data integrity just DROP IT!

[Ninjakreborn]

Yes, I have other issues as well. That's because I am trying to use the stupid site_id as both a primary key and everything else. I am going to create me another field which is an Auto-Incing normal ID and use it as my identifier. That'll make editing the site_id a lot easier.

I should have done that in the first place but this was an issue because I was taking over someone else's database which was quite an issue.