How do I use a DbValues Validator to check for database referential integrity errors

Dirk Fincley

I have a parent MySQL database table with a foreign key in a child table. When I delete a parent row which has associated child rows I get the MySQL error below:-

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (test.region, CONSTRAINT region_ibfk_country FOREIGN KEY (country_id) REFERENCES country (id))

Does anyone know how I should create a PHP Validator clause to check whether the delete would work and if not to be able to give a a user friendly error?

Please is there any example code. I could not find one?

allan

Hi,

The Editor libraries have a built in dbValues validator which you can use to perform this sort of validation.

The join example uses it (in its simplest possible form).

Allan

Dirk Fincley

As far as I can see there are no examples of dbValues being used for this, only to "enforce" referential integrity not to intercept a MySQL RI Violation.

allan

Oh sorry - I had misunderstood. At the moment, I'm afraid this would require a custom validation function to run before the Editor process runs - the PHP libraries do not currently provide a method for doing this.

For example you might use:

if ( Editor::action( $_POST ) === Editor::ACTION_DELETE ) {
  ... perform check against and return error if needed
}

This is an area where Editor's libraries are lacking at the moment I'm sorry to say. A new feature to address this is planned for 1.6.

Allan

Dirk Fincley

Thanks so I execute the "select" myself and simply return either an error message or not as the case may be. If I can get it to work I will post it on the forum. I bet other people may want to do something similar.

Where would I do the check? In my PHP or JS file?

Dirk Fincley

B the way, when is 16 version due?

allan

Thanks so I execute the "select" myself and simply return either an error message or not as the case may be.

Exactly. This would be done in the PHP.

B the way, when is 16 version due?

1.6 will likely be some months before it is released. I don't have an exactly time frame yet.

Regards,
Allan

Dirk Fincley

Many thanks.