how to implement foreign keys? (MySQL)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
lafflin
Forum Contributor
Posts: 123
Joined: Thu Jul 26, 2007 6:26 pm

how to implement foreign keys? (MySQL)

Post by lafflin »

I am a newb at both PHP and MySQL, and I am creating my first MySQL DB ( I have created one in Access prior to this).
I have just created all my tables, and I have my tables structure down with primary and foreign keys. and i'm satisified with all that
But the problem comes in when I started wondering where do I label my foreign keys? How does the MySQL engine know that a field is a foreign key? I feel like I'm missing something really obvious here. But what I mean is how do enforce referential integrity? and cascading updates? For instance, if I have a three tables (A,B,andC,) where the primary key for A is a foreign key for B, and C, and I delete a record in A then how do I ensure that the corresponding records in B and C will be deleted? Maybe there's a chapter about that in my book* that I just haven't gotten to yet, but I would think that is something I need to know when I design my tables, but I'm done the MySQL section of this book and they haven't given any syntax to cover anything like that other than to say that it's good practice to have pk somewhere in the name of the record.

I know that this might be to open ended of a question, but anything anyone can offer me would be greatly appreciated.

*my book covers both PHP, MySQL, and creating dynamic websites I have a basic understanding of PHP and MySQL.

Thanks in advance to any consideration given to this matter.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

1. There are no "foreign keys" in MyISAM DB.
2. You can use "foreign keys" in InnoDB DB.
There are 10 types of people in this world, those who understand binary and those who don't
lafflin
Forum Contributor
Posts: 123
Joined: Thu Jul 26, 2007 6:26 pm

Post by lafflin »

My book didn't mention that MyISAM didn't support foreign keys, which isn't cool cause I used MyISAM on all my tables because I wanted to make some of the fields full text searchable.
But my question is more about how to implement foreign keys. I have made my tables, and i have included the foreign keys as fields with the same data types and options as those same keys in their related tables, but I didn't specify anywhere that they are actually foreign keys. That's kinda what I'm wondering.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

You cant declare field as "foreign key" in MyISAM. If you use MyISAM then you will have to implement this logic into "code level", not into "DB level".
Sorry...
There are 10 types of people in this world, those who understand binary and those who don't
lafflin
Forum Contributor
Posts: 123
Joined: Thu Jul 26, 2007 6:26 pm

Post by lafflin »

Ok, Well I have been taking it slow because I know that I'm going to make some mistakes, so what I'm going to now do is make a a InnoDB version of my DB, and try both methods. But now I have a second question: Is implemeting FKs at the code level done with PHP or MySQL? MySQL I assume, but I'm too much of a newb to have safe assumptions though.

That said, provided I have an InnoDB DB and I am creating my tables, (and I have been creatin them in PHPMyadmin) how and where do I declare the fields to be used as FKs?
User avatar
iknownothing
Forum Contributor
Posts: 337
Joined: Sun Dec 17, 2006 11:53 pm
Location: Sunshine Coast, Australia

Post by iknownothing »

VladSun wrote:You cant declare field as "foreign key" in MyISAM. If you use MyISAM then you will have to implement this logic into "code level", not into "DB level".
MySQL = DB
PHP = Code
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
lafflin
Forum Contributor
Posts: 123
Joined: Thu Jul 26, 2007 6:26 pm

Post by lafflin »

Thanks Vlad, that was exactly what i was looking for. I couldn't find it on google. Once I started reading it and realized that my book makes no mention of constraints I googled it and found the same page.
Post Reply