Maintaining integrity when deleting from db

PHP
1

Hello fellow sitepointers,
I wonder if you might be able to help - I think I’m being dim.

I’m trying to write a piece of code, which will result in deleting a row (a property type) from a table, so long as its id is not referenced in either of two other tables within the same db. (the other two tables represent rental and sale properties - the website is for an estate agent).

This is what I’ve concocted, but it’s not working… Any ideas guys/gals? :slight_smile:

Thanks a mil!

Al


	/////////////////////////////////////////
	///////// DELETE PROPERTY TYPE //////////
	/////////////////////////////////////////
	
	if (isset($_POST['action']) and $_POST['action'] == 'Delete')
	{
		include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php';  
		$id = mysqli_real_escape_string($link, $_POST['id']);
		
		///Check to see if any properties currently using property type
		$sql = "SELECT sales.id FROM sales WHERE typeid='$id' AND rentals.id FROM rentals WHERE typeid='$id'";
		$result = mysqli_query($link, $sql);
		if (isset($result))
		{
			$deleteerror = 'You cannot delete this property type, as Sales properties exist which are currently using it.';
			include 'proptype-list.html.php';
			exit();
		}
		else {
		$sql = "DELETE FROM proptypes WHERE id='$id'";
		if (!mysqli_query($link, $sql))
		{
			$error = 'Error deleting property type.' . mysqli_error($link);
			include 'error.html.php';
			exit();
		}
		header('Location: .');
		exit();
		}
	}
2

You don’t have to do this through the application code. Instead, do it through the SQL server.

When you issue a CREATE TABLE make sure you’re using innoDB engine and use the FOREIGN KEY, ON DELETE CASCADE, that’s available to you.

Quick example:


CREATE TABLE user (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL
)
ENGINE = innodb

CREATE TABLE message (
  user_id INT UNSIGNED NOT NULL,
  message TEXT NOT NULL,
  CONSTRAINT fk_message_user_id
    FOREIGN KEY(user_id) REFERENCES user(id) ON DELETE CASCADE
)
ENGINE = innodb

I don’t mean to sound like captain obvious, but ON DELETE CASCADE causes a cascade affect to all the tables that referenced a deleted record.

3

Hi YaderBH,

Many thanks for your reply. That seems simple enough - I’m using PHPMyAdmin, so can presumably toggle those FK changes thru that?

One query obviously is this: how would I write code - php - to alert the user to what’s going on if the delete fails due to there being properties in other tables, that are associating themselves with that key?

Cheers in advance for your help :slight_smile:

Alex

4

that won’t happen with ON DELETE CASCADE, they’ll just be deleted

with ON DELETE RESTRICT, you get an error if there are any dependent rows (but i can’t recall the exact error message)

5

Hey. Sorry for the tardy reply.

…so I should infact be using ON DELETE RESTRICT, not ON DELETE CASCADE?
This all seems quite complex - would it not be easier to have some php code to check if the id of the row that’s trying to be deleted exists / is refrerenced in either of the other two tables and, if not, to go ahead and delete?

Sorry for not quite understanding yet - I’ll get there :slight_smile:

Thanks! :slight_smile:

Alex

6

if you want “if not, go ahead and delete” that’s ON DELETE RESTRICT

if you want “and the horse you rode in on” that’s ON DELETE CASCADE

:smiley: :smiley:

7

p.s. no it would not be easier to do it in php code

8

Haha - point taken :lol:

So, in terms of how i’d code the command, to throw the thing back at the user if there are associations when the delete is attempted, what would you suggest? I don’t know what the error would be you see :blush:

Thanks a million :slight_smile:

Alex

9

i don’t know the error code either, i’m sorry, i’m not a php developer

you could, you know, test it and find out…

:wink:

10

When you issue a DELETE query it will return the amount of rows it deleted from the table. However, it will not return the number of rows deleted from other tables caused by the cascade clause.

11

The error depends on what RDBMS you’re using, and what library you’re using in PHP. Just create a test-database with two tables, insert some data, try to delete it (using PHP) and see what the result is.

12

It’s the same as the error code in the MySQL server:

ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails

Which if you ask me, is a rather meaningless error… as with all MySQL InnoDB/foreign key errors, it’s very generic and gives no specifics…

If you want to give the user a specific error, you’ll still have to code to check exactly what it is that is preventing deletion.

13

I know the statement “show innodb status” will return information on the latest error in one of the InnoDB. Perhaps you could automatically execute that statement upon failure deleting (which you can recognise by the error code “1217”), and parse the error to determine exactly what has gone wrong?

I’m not sure if there are libraries that do this already, as I use PostgreSQL myself, which does return more specific information on what went wrong.

14

If I may join this discussion: if I understand correctly, for the kind of “restriction” the OP is looking for, there are two options:

  1. using the innodb feature of ON DELETE/UPDATE CASCADE/RESTRICT/etc
    But then you’d still have to program in PHP to check for the specific error code being returned by MySql in case of a FK restraint. And then deal (in PHP) with that situation

  2. Deal with the possible situation in PHP in the first place. That means first run a query to see if there’s a potential FK restraint situation, and then run the query that’s appropriate for that situation

I see the potential advantage in some situations of using the ON DELETE/UPDATE CASCADE/RESTRICT/etc but it’s not the whole solution, unfortunately. I’m not sure what I find nicer. Checking a specific innodb error code from within the PHP, or build in the necesarry login in PHP in the first place. Maybe the latter

15

Use foreign key constraints. The pros far out-weigh any perceived cons. Foreign keys ensure data integrity, and allow you to remove a certain amount of logic from your application (which may be duplicated in various places). In practice, you generally don’t have to get too fancy with checking for error codes and what not. The user interface should clearly portray the fundamental rules of the system, and so when a delete fails, the user should be able to deduce what went wrong, assuming you hinted at what went wrong, with a message such as ‘X could not be deleted’.

For example, if you’re application modelled an organisational chart, the user should know that employee’s have to be linked to a department (this should be made visually obvious). So if the user tried to delete a department which still had employee’s under it, then it should be pretty easy for the user to determine why the deletion failed. In fact, most users wouldn’t even attempt to perform such an action, in which case the error would likely only occur when a user does something on accident, or over looks something obvious.

Of course, telling the user exactly what went wrong is nicer, and in fact doing so isn’t that difficult. Even if you could get out of MySQL, exactly which table or record restricted the delete, you probably wouldn’t want to use that information in the error message, as there’s a good chance the user would still be confused - after all, they wouldn’t recognise the names of your database tables or the field ‘user_id’. You’d be better off using foreign key constraints just to enforce data integrity, rather than using it to enforce application logic also. When a foreign key constraint fails, you’d be best to determine in-code, exactly what failed, as you can then present much more meaningful error messages back to the user.

In other words, whether you use foreign key constraints or not, you should still determine the cause of a problem in-code as it allows for a better user experience. Using foreign key constraints is an added bonus, which enforces data integrity, and can remove some of that logic from your application. Foreign key constraints are easy to create, so there’s really no reason for not using them.

In other words, your application should still be aware of what relates to what in the database, but it doesn’t have to go as far to enforce that. Leave the job of enforcement to the database, and use the relationship information in your application only to determine the exact reason why a delete failed. Performing post-checks (on error), rather than pre-checks (always), will also reduce the number of queries between your application and the database.

16

@Wardrop: completely agree with what you say. Using foreign key constraints can help a lot in making sure the data remains correct.

Not sure if your post was a specific reply to what I said, but if so: what I tried to say is that you can’t do everything with them. Sometimes a lot of extra logic on the PHP side is needed to keep everything in check.

For example, if you have a system in which a user can add “visits” to “places”. So in the db you have a table for Visits (id, datevisit, user_id, place_id). Place_id is the FK to id in the table Places (id, name).

Now user 1 fills in a form and adds a visit to place X. After saving, there are two records, one in the visit table and one in the places table.

Now user 2 comes and fills in the form for a visit. Also to place X. After saving, Visits has 2 records, Places has 1.

But now user 1 realizes he made a mistake with the name of the place he visited. So he goes to the Edit form and changes the name of place X to Y. However, you can’t allow him to edit the name of the place, because the place is referenced by another user (user 2). Instead, a new record should be inserted for the changed name, while keeping the old name record. So now you have 2 records in the visits table and 2 records in the places table.

Anyway, it’s this kind of logic that is sometimes still needed in PHP to keep the data correct. In the above example, you would have to perform a couple of checks before saving a new visit:

  • is this place already in the db? If so, use that, if not add a new one
    Or when editing:
  • is this place being referenced by another visit? if not modify the existing place. If yes, try to add a new place. But before adding a new place, first check if that place already exists. Etc
17

Nah it wasn’t :slight_smile:

But your example, while interesting, seems to be an example of poor database (or possibly application) design. Not having a crack at you, but it’s just situation you should never find yourself in. I would never let a user create or edit a shared (shared in terms of “referenced by many”) database object like you demonstrated. I would either require that users select a place from a list of pre-defined places (not user defined, this guarantee a level of quality), or, if you wanted to allow users to define their own places in the event that the place they were looking for doesn’t exist, then I’d probably make the name of the place, the primary key of the places table. The solution though would depend heavily on the circumstance.

18

Thanks for your reply. In some ways I agree with you, but I’m not sure if I would call it “bad database design”. The point is, the end user doesn’t care about the database design. The end user doesn’t want (or should not have to) jump through hoops because the developer chose a certain storage system (relational database) with a certain table design.

With your suggestion, you’re asking the user to perform two tasks instead of one. First insert the place or check if the place already exists and then second, insert the visit. It’s easier for the user to just fill in one form and be done with it. Let the application code do the hard work and figure everything out in the back end. That’s were we have computers (and smart programmers) for isn’t it?

But maybe my example wasn’t really good. I can give another one. I have this application in which climbers can track the climbs they did. Each Climb is done on a date and consists of the successful ascent of a single Route. Each route is uniquely defined by a name, grade (difficulty) and the area it is located. So Area is the next thing. There are many area’s around the world, in different countries. Now in short, we get a couple of db tables:


Table Countries
id
name
Table Areas
id
country_id
name
Table Routes
id
name
area_id
Table Climbs
id
date_ascent
route_id
user_id
Table Users
id
username

So as you can see the climbs references the route, the route references the area, the area references the country.

Now the thing is, you start with an empty database. The only thing you know is a list of countries in the world. The names of areas and routes are not set. So users come to the application and want to add those. Now there are two ways to do that, the first one

  1. User goes to a form to add an area. Select one from the existing ones or add a new one if it doesn’t exist yet.
  2. User goes to a form to add a route. Select one from the existing ones or add a new one if it doesn’t exist yet.
  3. User goes to a form to add a climb. Select the route and then fill in the details of the climb (the date, maybe some notes, etc)

The second way is:

  1. User fills in one form with all data (select country,fill in area, route,climb data). The application saves everything and figures out what is new or not, what needs to be added or referenced, etc

So what would you prefer? The first method is easier for me the programmer. The second is easier for the user.

(p.s. not sure what “having a crack at” means, but I have no problem getting any critique, if that’s what it means)

19

I’m a user experience enthusiast. Creating a good user experience is why I program - it’s all about that end result for me. So obviously, I’d go with whatever provided the best user experience. However, the same thing as you suggested can be accomplished with my ‘name as primary key’ approach (or something like that). You shouldn’t need the trickery (and associated complexity) that you described in your earlier post.

Must be australian term :), but you guessed it. It means having a go at, or having a stab at, or in other words, criticizing (not necessarily constructive).

20

I’m not sure what you mean now. If you take my second example. You agree with me that presenting the user 3 different forms in 3 separate steps is not so friendly ok? Then the next question is, what exactly is the trickery and associated complexity you mean?