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.
dBforums
>
Getting a list of foreign key dependencies
02-02-09, 09:49
Linear Mode
