In this case, business_id should be both the primary key and a foreign key.
If you don't set it as a primary key, then the database would allow duplicate entries into the food_business table. By implementing this simple rule, you can prevent a whole class of application bugs from causing problems. On a different note, some databases will, on update, log the whole row if there is no primary key specified. This means that the transaction log can get very full very fast because all of the fields are logged even though only one field is changed. In short, since the field does uniquely identify each row in the table, it should be made the primary key. George Sexton MH Software, Inc. http://www.mhsoftware.com/ Voice: 303 438 9585 > -----Original Message----- > From: Asad Habib [mailto:[EMAIL PROTECTED] > Sent: Wednesday, June 15, 2005 5:19 AM > To: Stefan Kuhn > Cc: mysql@lists.mysql.com > Subject: Re: Primary and Foreign Keys (Follow Up) > > Hello. I appreciate your input. To clarify, I will provide you with an > example: > > I have a table called business and another one called > food_business. The > field business_id is a primary key of table business and a > foreign key of > table food_business. In this case, the foreign key is unique > and although > this is a one-to-one relationship, it would be inappropriate to merge > these 2 tables since food_business is a specialization of business and > will therefore contain fields that only apply to food > businesses and not > to any generic business. > > - Asad > > > On Wed, 15 Jun 2005, Stefan Kuhn wrote: > > > If your FK really is unique, you don't need two tables. Example > > First table Second Table > > ID FK > > 1 1 > > 2 2 > > 3 3 > > ... > > So you can make this one table. On other words, it would be > a one-to-one > > relation. And this would be one table. Only with a > one-to-many relation two > > tables make sense, but then your FK can't be unique. > > Stefan > > > > Am Wednesday 15 June 2005 12:41 schrieb Asad Habib: > > > As a follow up to my question, I did want to mention that > the foreign key > > > I am using is unique. > > > > > > - Asad > > > > -- > > Stefan Kuhn M. A. > > Cologne University BioInformatics Center > (http://www.cubic.uni-koeln.de) > > Zülpicher Str. 47, 50674 Cologne > > Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 > > My public PGP key is available at http://pgp.mit.edu > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
