Foreign keys in MySQL

Anon

I've got me a serious problem on hand!

When I create my tables in MySQL v3.23.32 I also define a number of foreign keys in which I state that certain tuples should be removed when the tuple it referes to is removed as well..

MySQL 'says' my queries check out and seems to parse them without a problem.
However, afterwards I can't seem to find whether or not I've defined these foreign keys anywhere.

Worst of all is that I can still DELETE rows from tables who should not be able to be removed at all!!

I really don't know what to do as I'm just starting to work with this DBMS.. The manual isn't a big help either. Can someone please help me?

Anon

What table handler are you using?

The Foreign Key support in MySQL is (at best) dodgy and is of limited use for keeping data integrity.

Deletes won't cascade and Foreign Keys are simply removed if violated by a DELETE statement, IIRC

Anon

Erm.. Table handlers?? I don't know what those are. If you are refering to data structures like MyISAM and such I can say that most of the tables have named structure..

But what you are saying is that foreign key support in MySQL is at best unreliable?

'Cause I really do need relationships between tables I can count on. Should I switch DBMS (to PostgreSQL f.e.)?

Thanks,

Jeroen

Anon

The MyISAM handler has no foreign key support. The InnoDB handler has some limited support, but I wouldn't rely on it. You may well be better using PostgreSQL

Anon

Okay.. Thanks a million for helping me out!

Anon

I have the same problem.
How can databases with complex structures be maintained without foreign keys?