MySQL Foreign Key

[Madatan]

Okey, so I tried to create a Foreign Key in MySQL by using the following commands:

FOREIGN KEY(keyname) REFERENCES tablename(keyname)

The table was create but foreign keys didn't work. Is foreign keys even supported by mysql and if it is how do I make one?

[wickning1]

I believe foreign keys are supported if you create an InnoDB table, but MyISAM does not support them. You can read about the various differences between InnoDB and MyISAM in the mysql documentation.

[Madatan]

[!--quoteo(post=351595:date=Mar 4 2006, 10:13 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 4 2006, 10:13 AM) [snapback]351595[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I believe foreign keys are supported if you create an InnoDB table, but MyISAM does not support them. You can read about the various differences between InnoDB and MyISAM in the mysql documentation.
[/quote]

I see, good to know. That helps alot, can I combine MyISAM and InnoDB or do I have to make all the connected tables of the same type? For example, can I have a foreign key in a InnoDB table that references to a MyISAM table?

[fenway]

Unfortunately not -- and that causes all sorts of weird errors to when trying to enforce referential integrity.

[Madatan]

I see, thanks for that info.

[lpxxfaintxx]

There is an alternative way, however.

Try this: [a href=\"http://mysqld.active-venture.com/example-Foreign_keys.html\" target=\"_blank\"]http://mysqld.active-venture.com/example-Foreign_keys.html[/a]

[fenway]

That "alternative" is NOT referential integrity -- it's just a JOIN statement. That is, using foreign keys is not equivalent in any way, shape or form to having referential integrity. We all use foreign keys all the time.