Jump to content
Larry Ullman's Book Forums

Recommended Posts

Hi Larry,

I am experimenting with foreign keys as per chapter 6 and am 'learning'. I thought that I would try to add that feature to a website that has a 'galleries' table and a 'works_of_art' table (as well as many other tables).  One gallery can have lots of works of art associated with it but a work of art can only 'belong' to one gallery - i.e., a one to many relationship. In my experimentation, I assigned a foreign key constraint in the works_of_art table to the primary key of the galleries table using CASCADE as I thought it would be nice that if a gallery were deleted all its associated works of art would also automatically get deleted.  But during my experimentation and 'playing around', I discovered that you cannot use a TRUNCATE table on the galleries table even if the works of art table is empty.

As I use the TRUNCATE feature in your database restore routine (I use it and the associated database backup routine in several websites), I'd appreciate your thoughts.

Thanks and Cheers from Oz.

Link to comment
Share on other sites

Hi again,  After some extensive Googling, the following works in my HeidiSQL - xampp - Windows 7 environment in localhost:


TRUNCATE table database_name.table_name;


But I'm wondering if this is a good solution?  And how would I incorporate these SQL statements into a PDO-executable PHP code?  (The truncate is no problem). The only place where I use the TRUNCATE is in the database restore module (from one of your earlier books).


Link to comment
Share on other sites

This worked but it seems clumsy.

$pdo1 = new PDO('mysql:host=' . DB_HOST . '; dbname=' . DB_NAME, DB_USER, DB_PASS);
  $ra = $pdo1->prepare($qa);
  if (!$ra->execute())
    $e_message = '$qa PDO database actions failed: ' . " - IP: $ip";
   } // end $qa/$ra failed
  $qb = "TRUNCATE table database_name.table_name";
  $rb = $pdo1->prepare($qb);
  if (!$rb->execute())
    $e_message = '$qb PDO database actions failed: ' . " - IP: $ip";
   } // end $qb/$rb failed
  $rc = $pdo1->prepare($qc);
  if (!$rc->execute())
    $e_message = '$qc PDO database actions failed: ' . " - IP: $ip";
   } // end $qc/$rc failed

Your thoughts and advice will be most appreciated.


Link to comment
Share on other sites

Hmmm...so I don't have a great answer for you here. With foreign key constraints in place, it's supposed to be a PITA to wipe out tables. Your solution seems to be okay. The only other thing I thiiiiiiink works is:

  1. Delete * from table B
  2. Delete * from table A (now that the constraints aren't an issue)
  3. Truncate both

I'm not positive that works all the time and it's not necessarily better.

Link to comment
Share on other sites

  • 4 weeks later...

Hi Larry,

Thanks for your thoughts (as always:-)

I have tested my routine above in tables with and without foreign key (FK) constraints and it works OK. But I found a way to check if a table has FK constraints (with thanks to stack exchange);

  CONSTRAINT_SCHEMA = 'database name'
  REFERENCED_TABLE_NAME = 'table name';

You can leave the table name off it you don't know if any tables have FK constraints, then in the returned data, look for non-NULL data elements for REFERENCED_COLUMN_NAME and REFERENCED_TABLE_NAME.



Link to comment
Share on other sites


  • Create New...