"Cascade" delete in MySql ?

[Anon]

MS Access has this type of delete or update, where (assuming your table relationships are defined correctly) that if you delete an entry in the master table, that particular related entry is also deleted in the slave table.

For example, if a person's telephone number is the primary key in table 1, and is also a foreign key in table 2, any deletion of that person's record in table 1 will also result in its deletion in table 2. This is quite handy to have, esp. for data integrity purposes! And this seems to occur as an MS Access transaction, meaning both entries are deleted OR the whole operation is aborted.

So does MySql have anything like this kind of operation (for either deletions or updates)? I could always do this as two separate transactions, but if this is otherwise available, why bother with the extra details?

[Anon]

There is no support for cascading deletes or updates in Mysql.

You have to use multiple queries within a transaction.

[[deleted]]

Providing you are using InnoDB, otherwise you won't have transactions either. (or indeed PK/FK relations in the first place)

The lack of cascading delete makes MySQL pretty useless, because you have to do the whole cascading thing in PHP, which means you have to write one of those for every PK/FK relation you have, which is.. wel... sh*te.

Consider using PostgreSQL instead, that supports the whole ball-of-wax, including transactions, cascading deletes etc etc...

---

A forum, a FAQ, email notification, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

[Anon]

Right now (except for the cascade deletes and updates) what I am doing is pretty simple. In fact, if I had not first worked with MS Access, I would not even be aware of some of these DB issues. Perhaps starting out as a "shrink wrap" VB developer, and then moving onto web development, does have some merit :-)

Question: how much PHP coding changes would it take if I suddenly switched my underlying DB from MySql to PostgreSql? Again, about all I am doing is connecting to the DB, connecting to a table, and then doing some simple queries. Would the port involve that much for PHP?

I'd just like to know, if and when I make the switch. And it seems I will, as it's always better to have "more" than "less" in this business.

p.s. how the hell do you pronounce that thing? Their .wav doesn't work on their web site. And They definitely chose a very poor name in terms of marketing. "Post-toasties DB Bytes" might have been better. But, what do I know :-)

[Anon]

I was afraid of that. But it can (and must be) done with careful coding.

Thanks anyway.

[Anon]

Actually, if you migrate from mysql to postgresql, you may find that you can remove huge chunks of code, since you'll have things like cascading deletes, FK/PK and all those other "real" rdbms things.

The wav file on the website works fine for me (and I'm running Linux, so it should work for just about anybody), but, to pronounce it, say: Post'-Gress-Que'-Ell (' shows accent)