0

Lets say you have a table(A) holding a limited amount of data, and one of the fields being a primary key is being referenced by not one, but many foreign keys from other tables.

The foreign keys of these tables have constraints against the before mentioned table(A) where should a row from table(A) be deleted and one of the other tables FKs holds a reference to it, the deletion will fail.

OK, with the database design taken care of, it is now a matter of the software, PHP in this case. An attempt is made to delete a row from table(A), how should a check be made to ensure that the row cannot be deleted if a reference is held?

I have seen cases where every table is checked before hand, however that involves coding in every check which becomes a maintenance nightmare. Additionally, I can't simply let the deletion be attempted, as this needs to be a cross database application, therefore the returned error codes wont be consistent.

I am interested to know how people usually tackle this scenario. I mentioned I am using PHP, but this situation should apply in any language.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Flosculus
  • 6,880
  • 3
  • 18
  • 42
  • Is using rollbacks and gathering error data by functions like `mysqli_error_list`, `mysqli_errno`, `PDO::errorInfo`, `PDO::errorCode` or `PDOStatement::errorCode` not an option? – inhan Oct 31 '12 at 01:49
  • it is an option, and it is what i first thought of, but i need to get around different databases returning different error codes for the same kind of operations. – Flosculus Oct 31 '12 at 11:48

1 Answers1

1

In general, you handle this by attempting to execute the DELETE statement on the PRIMARY KEY table, trapping for an exception, and rolling back the transaction when it happens. You can then report the failure in whatever form you want to the user. This works if all the referencing tables really declare the FOREIGN KEY back to the PRIMARY KEY table.

If you really need to check first you'll need to write code to "introspect" the keys that exist in the database, extract the table and column name(s) for each referencing foreign key, and issue SELECT COUNTs against each table. Unfortunately, each database engine has its own methods to learn about the database structure, so you'll have to write some code for each database engine you want to support.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • Both solutions are cases I wanted to avoid, but I'm guessing that applications designed with a specific database in mind with handlers for error codes returned when using your first solution. I will try the second solution for now, but if the amount of tables making the references become so large it become unfeasible, i will attempt a switch to the first solution and construct a list of error codes for specific databases and what they mean. Thanks – Flosculus Oct 31 '12 at 11:46
  • If your prepared to help me find a constant library of some kind which normalizes different database error codes to predefined constant declared against different values based on which PDO driver is being used, i'll raise it as another question and link it here. – Flosculus Oct 31 '12 at 11:57
  • The second solution does not require special coding for each referencing table. In effect, you read the list of referencing tables from the database itself. You only need to write the introspection code once (well, once for each engine) and it will work for all tables, and continue to work in the future when you add constraints. For a MySQL example see: http://stackoverflow.com/questions/201621/how-do-i-see-all-foreign-keys-to-a-table-or-column. – Larry Lustig Oct 31 '12 at 12:14
  • ah i see, so the returned values from "select TABLE_NAME,COLUMN_NAME" could be used to generate a query consisting of "SELECT COUNT" sub queries, or the job could be done within a single query using TABLE_NAME and COLUMN_NAME to dynamically change a nested where with a count against each one. While it sounds very clever, it does seem like a bit of a hack as its running queries against the database settings itself. Is there a way to make the first solution viable across multiple drivers? – Flosculus Oct 31 '12 at 12:50
  • from http://raymondkolbe.com/2009/03/08/sql-92-sqlstate-codes/ i can see that the error code class for the case above would be 23, however, as far as i am aware, not every pdo driver will return that. – Flosculus Oct 31 '12 at 12:51
  • In the first solution, you do not need to know anything about the exception to know that the statement failed. Therefore, you only have to trap for *any* exception, not for particular ones. Of course, that has the problem that the failure may be due to a non-foreign key issue. To handle that, you could either check for the phrase KEY in the exception text or, better, build a list of sufficiently unique strings representing an FK failure in each database engine, and check for and of the strings in the exception message. – Larry Lustig Oct 31 '12 at 12:53
  • The purpose of SQL requiring that the database metadata be available through the same querying mechanism as the database's data is to allow this kind of introspection, so I wouldn't call it a hack. That's what the metadata is *for*. If you implement this, you'd read the PK-FK structures at server start, not before every update. – Larry Lustig Oct 31 '12 at 12:55
  • In that case it does look good, i've tested it on SQL Server, I imagine it works on MYSQL, oracle however has strayed from SQL-92... I'll do some research into how people handle this cross database. thanks for your help. – Flosculus Oct 31 '12 at 14:25