function / object to update and delete table entries

Short question:

Is there a way to build an object and or function / function list (OR ANY EASIER WAY) that takes the trouble out of writing update and delete scripts for all your different tables as this is especially time consuming when you have a relational data model and entries in other tables may rely on what you are about to change or delete !
Who is Participating?
hernst42Connect With a Mentor Commented:
If the work is not done by the database, you have to do it in your application. So you will have to code the deletion by yourself. Maybe it helps if you store the information about the references in an array e.g.
$riref['referencedtable']['referencedfield']['basetable'] = basefield;
for my example
$riref['tab1']['PK']['tab2'] = FK_tab1;
so tab.PK is used by tab2.FK

When we delete in tab1 we call recdelete('tab1','PK', 'PK=1');

Then write a generic function that deleted all depending records like the following (pseudocode)

function recdelete($table, $field, $condition) {
    global $riref;
    if (isset($riref[$table][$field])) {
        // first the values that exists for this condition need to be known
        SELECT DISTINCT $field FROM $table WHERE $condition;
        $id = array();
        while ($row = mysql_fetch_array()) {
            $id[] = $row[$field];
        if (count($id)) {
            foreach ($riref[$table][$field] as $tabref => $fieldref)) {
                recdelete($tabref, $fieldref, "$fildref in ( ".implode(',', $id). ')';
    DELETE FROM $table WHERE $condition;

Updates are much more complicated but may be done the same way.

One additional note:
If one step of that delete or update failes you will get an inconsitend database and an recover-mechanism is very hard to implement (like transactions supported by Innodb).
DelacourtAuthor Commented:
database would be mysql !!!
DelacourtAuthor Commented:
i.e. having ten tables, where table 1 relates to table 2, table 2 relates to table 3, table 3 relates to table 4, and so forth (where the table with the lower numbers id is in the next table as a foreign key)

problem, how would i be able to get a message about table10 having something to do with one of the tables with a lower number, before i go ahead and update and delete it, thereby losing what was in table10 ?

is my explanation working here ... ? , pls let me know :)

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Are you talking about getting the ID of an SQL insert statement, so you can update the next table with the new ID ? ( mysql_insert(); )
The database can handle all those things if you have support for InnoDB-Tables in Mysql. This is the fastest and error-less way to do it

See and there the part about referencial integrety

CREATE TABLE tab1 (PK int not null auto_increment, Disabled char(1) default 'N' not null,Host varchar (20) null,Id varchar (20) not null,lastlogin int null,Password varchar (40) null, primary key( PK )) TYPE = InnoDB;
CREATE TABLE tab2 (PK int not null auto_increment, FK_tab1 int null,Type char(1) null, INDEX (FK_tab1) , FOREIGN KEY (FK_tab1) REFERENCES tab1(PK) ON DELETE cascade ON UPDATE cascade, primary key( PK )) TYPE = InnoDB;
DelacourtAuthor Commented:
Thanks Guys,

To answer your question Gitcho: nope, I think I would use isset() for that (methinks:)
Looking more at the edit / delete process

Hernst42: Thanks for that, I dont have innoDB tables, and not going to be using them, however, is there another way, other than the tedious coding of all edit delete statements per table, to keep referential integrity of my tables entries without using innoDB ?
DelacourtAuthor Commented:
tabletype: myisam
DelacourtAuthor Commented:
thanks for that, i am going to try and write this using your above suggestions, but was also thinking along the lines of

choosing something to delete or edit, then this would be passed to a page before going ahead with the execution of the code: essentially something like this:

when deleting from table 4:
having a select table10_field_name from table10, table9, table8, table7, table6, table5, table4
where table4.table4id = table5.table4id
and table5.table5id = table6.table5id
and table7.table7id = table8.table7id
and table9.table9id = table10.table9id

1. then if there is a field name, say, you must delete result[table10_field_name] first
2. or say, you are free to delete this item

what happens one level up, say there is nothing in table10, what about table9 ???

thats what i am getting at: let me know if you think this is feasible at all !

DelacourtAuthor Commented:
i meant in second last line, what happens if there is something in table9 that requires referential integrity

also, how would i go about deleting something from table2 in the same way, would it need to be hardcoded, or could i write some type of function ??

Thanks again for all your help Hernst42

I've never seen a DB schema that required that much logic for deletes.   Are you sure your DB is in 3rd normal ? Unless this is a supremely complicated app, there's not normally that much logic required.  Hard to know though, as not much data provided.
DelacourtAuthor Commented:
Gitcho, just left a comment for you on another question of mine, I have some points for oyu, let me know where to put them up !

Basically, my tables are like this: each with a foreign key from the table above (product owned by suburb - thereby also by country)
also, each table before product also has their own images table for a pic gallery, so, how would i got about it !!! :)


another thing: each with a pic gallery table (i.e. city_pic_table) would it be better to have one, with Foreign Keys from each of the tables that would require images ?
Awesome ! Thanks Delacourt - I only need a few more points to get my membership !  

Regarding this database, which tables have 1 -> 1 relationships ? 1 -> many ? many -> 1  ?

Depending on your business rules, instead of tying all the foreign keys together in the same table, you may want to set up a lookup table with all your foreign keys that ties these tables together.

Regarding the images table :  " another thing: each with a pic gallery table (i.e. city_pic_table) would it be better to have one, with Foreign Keys from each of the tables that would require images "  ... I would agree.  
I think you are looking for triggers. They are not yet implemented in mysql but they will be:

With triggers, you can define functions (triggers) that are stored somewhere in the database and they are automatically called. I don't know the syntax but it would be something like

Then when you execute "DELETE FROM table1 where id=2" entries in the other tables will automatically be deleted too.

You'll still have to write 10 triggers (well maybe they can be put in 1 statement too), but then you can delete from everywhere while deleting only entries from table1.

In Oracle you can define triggers.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.