mysql and foriegn keys

rhaggert

whats the deal with mysql and foriegn keys?

do they not work right/
do you have to do something special with the tables to get them to work?
are there any restrictions with foriegn keys and mysql?

i do it in postgres just fine but mysql seems to be quirky..

can some one tell me whats up with mysql and foriegn keys?

Thanks

Bunkermaster

you have to code the relations in the SQL using LEFT JOIN

Sxooter

Actually, I think you can use some limited form of fks, but they only work on innodb to innodb tables.

I.e. you can't do it on myisam tables at all.

Note that you don't get cascade actions or some of the other advanced features of fks just yet, but that can be worked around with a simple transaction. now if MySQL only had triggers...

rhaggert

so if i use InnoDB then i can use foreign keys to at least protect from
some bad things happening.. like inserting something that references
something in another table thats not there.

perhaps i messed it up but it didn't show me any data integrity. mysql
just inserted something bad anyways...

i read something about mysql and innodb.. if you alter the table bad thing
happen with innodb, like you loose any integrity you had.

any one know of any truth to that?

mysql.com kind of put some light on it but other than that i haven't heard
anything else about it.

is it a bad idea to wing the ideas of what foriegn keys do into php script?

thanks

Sxooter

Yeah, it is a pretty bad idea, but it can be done.

What you'll basically be doing is trying to write your own database in some ways.

Any chance you can get postgresql 7.3.3 on that box? It has very fast performance, both with and without foreign keys.

The whole "silent failure" thing in MySQL drives me nuts. At first, the fact that Postgresql bitched at me every three seconds was a pain, but later on, when I used MySQL for things that I thought were getting done but weren't, I was most unhappy.

I'd rather have an error than a silent failure. If you've got to writie your own foreign key stuff, at least make sure it fails loudly so the user knows what broke.

rhaggert

well im not admin for the box so its mysql running on win2k.

i started out learning sql with mysql but it was such a headache to get things to do what sql is suppose to do. then i switched to postgres. it seems to be a little closer to sql than mysql. from what ive seen

i guess future releases of mysql are supposed to catch up to postgres... if they ever get it out

my idea was to write a couple of funtions as wrappers to mysql inserts... like do some queries on the db first and if its not good report an error.

Sxooter

There is a native windows version of Postgresql due out with the 7.4 code base. There was a proof of concept version released based on 7.2.1 by peer direct.

These versions are NOT cygwin dependent, and seem pretty fast.

I think the real possibility of MySQL having all the features of postgresql are about zero. By the time you've bolted on transactions, fks, constraints, triggers, stored procedures, user extensible data types, schemas, unions, sub selects, ANSI SQL compliant sequences, write ahead logging, transactable DDL, and a dozen other features, MySQL won't be MySQL anymore, it'll be a slow, bloated database.

Postgresql started out with those features programmed properly, if a bit slow, and has slowly been optimized to perform well while doing those dozens of things that MySQL doesn't do. Adding the feature is more than just tacking it on the side, it has to truly be integrated into the engine, and integrated efficiently. Often, choices made at the beginning of designing a database will restrict what you'll be able to add to it later, and MySQL was not made with things like transactions or fks in mind.

I like MySQL for data storage of things like web content, but I just don't trust it for real database work with heavy parallel access and such.

Sadmin

you have been talkin about mysql /innoDB and foreign keys and it looks possible to got fkeys
which Software are you using...
somebody has a mysql foreign keys tutorial? -

phpmyadmin offers "table linking" but (version 2.5.0) show this to me:
Error The additional Features for working with linked Tables have been deactivated. To find out why click
it shows me
$cfg['Servers'][$i]['relation'] ... not OK [ Documentation ]
General relation features: Disabled

... an other vars
i've change it many times (in the config.php files), but it doesn't shows the changes.

someone in this problem, some hints?
thanks a lot
sadmin colombia.