cascade delete using foreign keys

[bobby]

i have been using Mysql for an application I am trying to implement. For the last few weeks I have understood that mysql aint any good with foreign keys and cascade deletes and updates. Then I recently read it can be done effectively with something like:

CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB;

can anyone explain the InnoDB - hows it all tie into Mysql. Does this need to be stated as tables are created - does it keep consistency effectively - as up till now I was going to use nested queries going through all the relevant tables.

Many Thanks

Bob

[raf]

Nobody digs into this, so i'll give it e try.

First of all, there are a few things i didn't understand.
mysql aint any good with foreign keys and cascade deletes and updates
??
Maybe you should read this. There are some useful links inhere to all the items you mention.
http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html

In MySQL, you can not (yet) set up relationships.

About InnoDB : MySQL supports multiple tabletypes, they each are managed by different DBM's. Each type has his advantages
Below is some info from the mysql.com site about this speciffic subject

It's up to the DBA to decide what the most important : speed and diskspace or safety and recovery

How to set up the different tables and indexes is also available on mysql.com
http://www.mysql.com/doc/en/CREATE_TABLE.html
you van also change the tabletype, using the alter table command.

Think it's best you browser around abit on there site toget all the ins and outs

------------------------------------------------------------------------------------
Note that MySQL supports two different kinds of tables: transaction-safe tables (InnoDB and BDB) and not transaction-safe tables (HEAP, ISAM, MERGE, and MyISAM).

Advantages of transaction-safe tables (TST):

Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup + the transaction log.
You can combine many statements and accept these all in one go with the COMMIT command.
You can execute ROLLBACK to ignore your changes (if you are not running in auto-commit mode).
If an update fails, all your changes will be restored. (With NTST tables all changes that have taken place are permanent)
Can provide better concurrency if the table gets many updates concurrently with reads.
Note that to use InnoDB tables you have to use at least the innodb_data_file_path startup option. See section 7.5.3 InnoDB Startup Options.

Advantages of not transaction-safe tables (NTST):

Much faster as there is no transaction overhead.
Will use less disk space as there is no overhead of transactions.
Will use less memory to do updates.
You can combine TST and NTST tables in the same statements to get the best of both worlds.

[bobby]

Im really a novice....but if Mysql does not (yet) support relationships...then what are the foreign keys used for. I thought the whole point of foreign keys was to display the relationships between tables. How does it do the cascade stuff if it does not support foreign keys



I really am worried about in my application a user is deleted that all relevant data is removed. Now I could probably do this with a query that deletes from all the relevant tables using the join sql syntax.

Sorry if I am totally missing the point and being totally naive and thanks for your response.

[raf]

bobby,

those are all valid questions and concerns.
The first link in my post answers all these questions.

Indeed, if you can't use them for relationship's they are quite pointless. But personally, i never define relationships in the db. Just use joins in my app's embedded sql. I remember having ptoblems in the past with relationships. (Thought it was a query on three table where two of the tables had a foreign key relationship to the same primary key in the third table. Youcan't then use some other joins in your sql statements.) So i just work with joins in my app.

On deleting

"For MyISAM tables, you can work around the lack of ON DELETE by adding the appropriate DELETE statement to an application when you delete records from a table that has a foreign key. In practice this is as quick (in some cases quicker) and much more portable than using foreign keys. "

"In MySQL Server 3.23.44 and up, InnoDB tables support checking of foreign key constraints, including CASCADE, ON DELETE, and ON UPDATE. See section 7.5 InnoDB Tables."

So it's up to you to decide what's the most important : speed an diskspace or security and cascades.