Jump to content


Photo

MySQL Foreign Key


  • Please log in to reply
6 replies to this topic

#1 Madatan

Madatan
  • Members
  • PipPipPip
  • Advanced Member
  • 51 posts

Posted 04 March 2006 - 12:25 PM

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?

#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 04 March 2006 - 03:13 PM

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.

#3 Madatan

Madatan
  • Members
  • PipPipPip
  • Advanced Member
  • 51 posts

Posted 08 March 2006 - 05:26 PM

[!--quoteo(post=351595:date=Mar 4 2006, 10:13 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 4 2006, 10:13 AM) View Post[/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?

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 08 March 2006 - 07:45 PM

Unfortunately not -- and that causes all sorts of weird errors to when trying to enforce referential integrity.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 Madatan

Madatan
  • Members
  • PipPipPip
  • Advanced Member
  • 51 posts

Posted 09 March 2006 - 11:00 AM

I see, thanks for that info.

#6 lpxxfaintxx

lpxxfaintxx
  • Members
  • PipPipPip
  • Advanced Member
  • 181 posts

Posted 09 March 2006 - 11:39 AM

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]

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 March 2006 - 02:23 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users