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.
how to implement foreign keys? (MySQL)
Moderator: General Moderators
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.
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.
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?
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?
- iknownothing
- Forum Contributor
- Posts: 337
- Joined: Sun Dec 17, 2006 11:53 pm
- Location: Sunshine Coast, Australia