Foreign Keys and backups/restores

[Necuima]

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.

[Necuima]

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

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table database_name.table_name;

SET FOREIGN_KEY_CHECKS = 1;

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).

Thanks

[Necuima]

This worked but it seems clumsy.

$pdo1 = new PDO('mysql:host=' . DB_HOST . '; dbname=' . DB_NAME, DB_USER, DB_PASS);
  $qa = "SET FOREIGN_KEY_CHECKS = 0";
  $ra = $pdo1->prepare($qa);
  if (!$ra->execute())
   {
    $e_message = '$qa PDO database actions failed: ' . " - IP: $ip";
    add_error("$e_message");
   } // 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";
    add_error("$e_message");
   } // end $qb/$rb failed
  $qc = "SET FOREIGN_KEY_CHECKS = 1";
  $rc = $pdo1->prepare($qc);
  if (!$rc->execute())
   {
    $e_message = '$qc PDO database actions failed: ' . " - IP: $ip";
    add_error("$e_message");
   } // end $qc/$rc failed

Your thoughts and advice will be most appreciated.

Cheers

[Larry]

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.

[Necuima]

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);

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  CONSTRAINT_NAME,
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  CONSTRAINT_SCHEMA = 'database name'
  and
  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.

Cheers

 

[Larry]

Awesome! Thanks for sharing!