Jump to content
Larry Ullman's Book Forums

Foreign Keys


Recommended Posts

Hi Larry,

Happy New Year!

Hope you had a great festive season!

Larry, I am still learning php and mysql, so please forgive me if the questions seem really basic, but I've tried to research and understand certain concepts, but I'm still confused.

My understanding of foreign keys in a database is that:

1) They provide referential integrity.

2) It is used to link two tables together.

3) It will ensure that a table referencing another table will always be referencing a row that exists in its own table, and a table will never be orphaned.

Now in your book: 'PHP and MySQL for Dynamic Web Sites' which I purchased recently. On page 198, you created a foreign 'customer_id' key in the 'accounts', table that references the 'customers' table.

I understand this concept and it makes sense.

 

But on page 170 in the 'Effortless E-Commerce' book, you again, created a foreign 'customer_id' key in the 'orders' table that references the 'customers' table on page 169.

 

So you have different methods and approaches in both books, but they do the same thing!

 

I'm really confused by this because i'm thinking:

1) How can the foreign 'customer_id' key in the 'orders' table, on page 170('Effortless E-Commerce'), reference the 'customers' table without explicitly referencing it like how you did it on page 198 in the 'PHP and MySQL for Dynamic' book?

2) I've never seen a foreign key get created in this way on page 170 in 'Effortless E-Commerce' book, so can you explain to me

 

i) How this works, ie, how does the database knows 'customer_id' is a foreign key, and that it is referencing the 'customers' table on page 170, without you explicitly writing it.

I looked in phpmyadmin, and looked in the designer veiw of your database, but there are no links or relationships demonstrated, or shown between any of the tables, even though the diagram on page 167 in the 'Effortless E-Commerce' book, clearly states that there a one-to-many relationship between the tables.

 

3) So why doesn't the tables in the Ecommerce2 database in phpmyadmin (in the designer veiw, or even in the database tables), not reflect or show the one-to-many relationships that you have stated exists between the tables on page 167.

 

Thank you in advance

regards

Link to comment
Share on other sites

  • 1 month later...
  • 3 weeks later...

Hi Larry,

I just saw your message! Didn't think you were going to reply so I didn't bother to check up!

 

No problem anway!

Yes, I'm still really confused, because in sql, as I understand it, a reference to a FOREIGN key has to be explicity created, so in your example I'm really confused as to how the Foreign key references are created!

 

Thanks

Link to comment
Share on other sites

  • 2 weeks later...

Sorry for the delay. I wanted to remind myself of why I did what I did...which I may now remember. Maybe! I'm still a little bit confused b/c the page numbers you reference for the "Effortless E-Commerce" book don't match up with those in the printed copy of the 2nd edition. But I think I get what's going on...by looking at the SQL commands for the second example in the "Effortless E-Commerce" book, (which is the one with "customers" and "orders" tables).

Foreign keys are a (normalized) database concept, in which a column in table A represents a column from table B. There are also foreign key *constraints*, which is an aspect of the database application, enforcing the existence of a foreign key. This is what you're seeing on page 197- in the "PHP and MySQL" book. 

The SQL in the "Effortless E-Commerce" book has foreign keys but not foreign key constraints. There's a tip on page 193 that says you could add these. As a rule, a database must have foreign keys in order to use foreign key constraints but a database can have foreign keys without foreign key constraints. 

Separately, and perhaps the confusion here, MySQL will use the word "KEY" as synonymous with "INDEX". This is just saying that MySQL should index--i.e., track--the values in that column. Because, as a good rule, you should index foreign key columns.

So! The reason everything seems to be behaving differently for the "Effortless E-Commerce" tables is because they don't have foreign key *constraints*. 

Let me know if this is still not clear or if you have any other questions (and I'll reply more promptly, I promise!).

Link to comment
Share on other sites

 Share

×
×
  • Create New...