RE: Primary and Foreign Keys (Follow Up)
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]
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 > IDFK > 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]
Re: Primary and Foreign Keys (Follow Up)
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]
Primary and Foreign Keys (Follow Up)
As a follow up to my question, I did want to mention that the foreign key I am using is unique. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Primary and Foreign Keys
Is it good practice to have a primary key for a table even though the table has a foreign key that is the primary key of another table? If so, why is this the case? I would appreciate if you point me to some resources that point out the advantages and disadvantages of both approaches. Thank you. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating primary and foreign keys for MySQL
You haven't found the online manual yet, have you? http://dev.mysql.com/doc/mysql/en/index.html Specifically the documentation for the command you are curious about is found here: http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Herman Scheepers <[EMAIL PROTECTED]> wrote on 10/13/2004 02:54:17 PM: > Hi All > > I am MySQL newby with an Oracle background. > > Is the following syntax suported? > > alter table members add primary key (id); > alter table members add constraint members_uk unique > (name); > > Thanx > Herman > > > > __ > Do you Yahoo!? > Yahoo! Mail Address AutoComplete - You start. We finish. > http://promotions.yahoo.com/new_mail > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Creating primary and foreign keys for MySQL
Hi All I am MySQL newby with an Oracle background. Is the following syntax suported? alter table members add primary key (id); alter table members add constraint members_uk unique (name); Thanx Herman __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Primary and Foreign Keys in MySQL
I'm assuming you have AUTO_INCREMENT columns in each table? Try this: INSERT INTO TABLE1(...) VALUES(...); SELECT @t1id := LAST_INSERT_ID(); INSERT INTO TABLE1A(table1_id, ...) VALUES(@t1id, ); INSERT INTO TABLE1B(table1_id, ...) VALUES(@t1id, ); ... -JF > -Original Message- > From: Aamer Rauf [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, July 24, 2002 12:55 PM > To: [EMAIL PROTECTED] > Subject: Primary and Foreign Keys in MySQL > > > Hello, > > I am working on MySQL database. I have come across a 'problem' > but don't know > how to get around that. The problem is the following: > I have a table, say TABLE1, with primary key being called as table1_id. > Now there are other tables, like TABLE1A, TABLE1B, TABLE1C etc.. > where I want to > use primary key of TABLE1 (i.e table1_id) as foreign key. I see a > problem in > using the function LAST_INSERT_ID(). Lets say I use it to insert > a record in > TABLE1A, that will work fine. But now if I use it to put a record > in TABLE1B it > would give me the primary key ID of TABLE1A rather than TABLE1. > Please help me > out on this. > > Aamer > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Primary and Foreign Keys in MySQL
Hello, I am working on MySQL database. I have come across a 'problem' but don't know how to get around that. The problem is the following: I have a table, say TABLE1, with primary key being called as table1_id. Now there are other tables, like TABLE1A, TABLE1B, TABLE1C etc.. where I want to use primary key of TABLE1 (i.e table1_id) as foreign key. I see a problem in using the function LAST_INSERT_ID(). Lets say I use it to insert a record in TABLE1A, that will work fine. But now if I use it to put a record in TABLE1B it would give me the primary key ID of TABLE1A rather than TABLE1. Please help me out on this. Aamer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
