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]
