Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Posts
    2

    Unanswered: Getting a list of foreign key dependencies

    Hi,

    I'm trying to write a code generator in Python that will take a MySQL database, and build INSERT, UPDATE and DELETE code for PHP. The Insert and Update code is fairly easy, but I'm having trouble with the Delete code generation, specifically where foreign keys are involved.

    Let's say I have these tables in MySQL:

    Code:
    Authors (
      AuthorID: int auto_increment primary_key
      Name: varchar
    )
    
    Books (
      BookID: int autoincrement primary_key
      AuthorID: int, foreign_key references Authors.AuthorID
      Title: varchar
    )
    Now, say I delete a row from the Books table. No problem, the foreign key on AuthorID references a row in the Authors table, so the delete command (assuming we're deleting Book #123) is:
    Code:
    DELETE FROM Books WHERE BookID = 123
    But if I delete a row from the Authors table, first I have to delete all the rows in Books that reference that author. So for Author #456:

    Code:
    DELETE FROM Books WHERE AuthorID = 456
    DELETE FROM Authors WHERE AuthorID = 456
    Before I go play games with depth-first tree-traversal algorithms and other such things, does anyone know how I could find out what rows I'd have to delete (and from which tables), or even a list of the constraints involved?

    I don't like the idea of using ON DELETE CASCADE (I'm using ON DELETE RESTRICT, which ISTR is the default for MySQL5), as a small software bug could conceivably destroy large amounts of data (or even a small amount of important data). Frankly I'd rather see a database error and be forced to go bug-hunting than lose half my database...

    Can anyone help?

    Thanks,
    Phil.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you will find a lot more problems waiting for you than just "a small software bug could conceivably destroy large amounts of data"

    many mysql developers don't bother to define foreign keys

    why? because in myisam tables they don't work at all, so why bother, and in innodb tables they're a nuisance, they could "conceivably" interfere with the developer's goal of adding data without running into those stupid FK errors



    so if your purpose is to generate code for mysql databases, and you realize it will only work if the FKs have actually been declared, then i would advise you to go ahead and use ON DELETE CASCADE

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Posts
    2
    Quote Originally Posted by r937
    why? because in myisam tables they don't work at all, so why bother, and in innodb tables they're a nuisance, they could "conceivably" interfere with the developer's goal of adding data without running into those stupid FK errors
    To be honest, I'm using the FKs mainly to prevent stupid coding bugs during development. The idea being that if I (using the example in #1) try and delete an Author record when a Book is referencing it, the delete fails and I get a big annoying error alert (and the PDO exception handler dumps a ton of debug data to disc so I can actually fix the bug.. hopefully )

    What I don't want is to have invalid (or redundant) data in the DB -- in the example I gave above, a Book referencing an Author that had been deleted. Relying on ON DELETE CASCADE means that if foreign keys aren't working, the CASCADE is completely ignored and the database is rendered inconsistent.

    Essentially, I'm lazy and I want to get the machine to deal with the boring, repetitive tasks (building the data abstraction classes, read/write/delete code, and so on) leaving me to deal with the HTML and page logic. Hence why I'm writing a code generator instead of just writing the DB access code...


    EDIT: Ultimately the generated code shouldn't care if the DB is enforcing keys or not. What I want to do is have my codegen look at my development environment (which uses InnoDB), figure out what references what, and generate the code necessary to perform the operation the user requested (i.e. delete a row from a table) while keeping all the other data consistent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •