how to implement foreign keys? (MySQL)
Posted: Mon Aug 06, 2007 6:53 pm
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.
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.