473,320 Members | 1,713 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

mysql foreign key

pradeepjain
563 512MB
i have 1st table created like this
Expand|Select|Wrap|Line Numbers
  1. create table mobiles(property1 varchar(100) NOT NULL,property2 varchar(100) NOT NULL,property3 varchar(100) NOT NULL,property4 varchar(100) NOT NULL,property5 varchar(100) NOT NULL,property6 varchar(100) NOT NULL,property7 varchar(100) NOT NULL,property8 varchar(100) NOT NULL,property9 varchar(100) NOT NULL,property10 varchar(100) NOT NULL,property11 varchar(100) NOT NULL,property12 varchar(100) NOT NULL,property13 varchar(100) NOT NULL,property14 varchar(100) NOT NULL,property15 varchar(100) NOT NULL,PRIMARY KEY (property1,property2))engine=innodb;
and 2nd table created like this!

Expand|Select|Wrap|Line Numbers
  1. create table mobile_rating(property1 varchar(100) NOT NULL,property2 varchar(100) NOT NULL,rating1 int NOT NULL,rating2 int NOT NULL,rating3 int NOT NULL,rating4 int NOT NULL,rating5 int NOT NULL,rating6 int NOT NULL,rating7 int NOT NULL,rating8 int NOT NULL,rating9 int NOT NULL,rating10 int NOT NULL,rating11 int NOT NULL,rating12 int NOT NULL,rating13 int NOT NULL,rating14 int NOT NULL,rating15 int NOT NULL,FOREIGN KEY(property1,property2) REFERENCES mobiles(property1,property2))engine=innodb;
i wanted to create a primary key in mobiles table which is combination of (property1,property2) the 1st table looks like .i.e mobiles
Expand|Select|Wrap|Line Numbers
  1. +------------+----------------------------------------------------------------------------------------+------+-----+---------+-------+
  2. | Field      | Type                                                                                   | Null | Key | Default | Extra |
  3. +------------+----------------------------------------------------------------------------------------+------+-----+---------+-------+
  4. | property1  | varchar(100)                                                                           | NO   | PRI | NULL    |       | 
  5. | property2  | varchar(100)                                                                           | NO   | PRI | NULL    |       | 
  6. | property3  | enum('Basic','Smart','Camera','Music','PDA')                                           | YES  |     | NULL    |       | 
  7. | property4  | enum('2000-5000','5000-10000','10000-15000','15000-20000','20000-25000','25000-30000') | YES  |     | NULL    |       | 
  8. | property5  | enum('CDMA','GSM','BOTH')                                                              | YES  |     | NULL    |       | 
  9. | property6  | enum('Android','Symbian','Linux','Windows''Blackberry')                                | YES  |     | NULL    |       | 
  10. | property7  | enum('Block','Slide','Clamshell','Sideflip')                                           | YES  |     | NULL    |       | 
  11. | property8  | varchar(100)                                                                           | NO   |     | NULL    |       | 
  12. | property9  | varchar(100)                                                                           | NO   |     | NULL    |       | 
  13. | property10 | varchar(100)                                                                           | NO   |     | NULL    |       | 
  14. | property11 | varchar(100)                                                                           | NO   |     | NULL    |       | 
  15. | property12 | varchar(100)                                                                           | NO   |     | NULL    |       | 
  16. | property13 | varchar(100)                                                                           | NO   |     | NULL    |       | 
  17. | property14 | varchar(100)                                                                           | NO   |     | NULL    |       | 
  18. | property15 | varchar(100)                                                                           | NO   |     | NULL    |       | 
  19. +------------+----------------------------------------------------------------------------------------+------+-----+---------+-------+
but the second table looks like i.e mobile_rating
Expand|Select|Wrap|Line Numbers
  1.  
  2. +-----------+--------------+------+-----+---------+-------+
  3. | Field     | Type         | Null | Key | Default | Extra |
  4. +-----------+--------------+------+-----+---------+-------+
  5. | property1 | varchar(100) | NO   | MUL | NULL    |       | 
  6. | property2 | varchar(100) | NO   |     | NULL    |       | 
  7. | rating1   | int(11)      | NO   |     | NULL    |       | 
  8. | rating2   | int(11)      | NO   |     | NULL    |       | 
  9. | rating3   | int(11)      | NO   |     | NULL    |       | 
  10. | rating4   | int(11)      | NO   |     | NULL    |       | 
  11. | rating5   | int(11)      | NO   |     | NULL    |       | 
  12. | rating6   | int(11)      | NO   |     | NULL    |       | 
  13. | rating7   | int(11)      | NO   |     | NULL    |       | 
  14. | rating8   | int(11)      | NO   |     | NULL    |       | 
  15. | rating9   | int(11)      | NO   |     | NULL    |       | 
  16. | rating10  | int(11)      | NO   |     | NULL    |       | 
  17. | rating11  | int(11)      | NO   |     | NULL    |       | 
  18. | rating12  | int(11)      | NO   |     | NULL    |       | 
  19. | rating13  | int(11)      | NO   |     | NULL    |       | 
  20. | rating14  | int(11)      | NO   |     | NULL    |       | 
  21. | rating15  | int(11)      | NO   |     | NULL    |       | 
  22. +-----------+--------------+------+-----+---------+-------+

i dono if the foreign key in second table is combination of (property1,property2) ...but i tried to create it like that ...it shows as

Expand|Select|Wrap|Line Numbers
  1. property1 | varchar(100) | NO   | MUL | NULL    |       | 
  2. | property2 | varchar(100) | NO   |     | NULL    |       | 
i.e MUL for property1 but nothing for property2.. can i know is the foreign key is combination of those 2 fields!!?
Oct 16 '09 #1

✓ answered by Atli

Hey.

You left out the PRIMARY KEY part of your second query.
That is why the table explanation doesn't list any PRI columns, because there is no primary key.

5 4000
Atli
5,058 Expert 4TB
Hey.

You left out the PRIMARY KEY part of your second query.
That is why the table explanation doesn't list any PRI columns, because there is no primary key.
Oct 21 '09 #2
pradeepjain
563 512MB
Expand|Select|Wrap|Line Numbers
  1.  create table mobile_rating(id int NOT NULL AUTO_INCREMENT PRIMARY KEY,property1 varchar(100) NOT NULL,property2 varchar(100) NOT NULL,rating1 int NOT NULL,rating2 int NOT NULL,rating3 int NOT NULL,rating4 int NOT NULL,rating5 int NOT NULL,rating6 int NOT NULL,rating7 int NOT NULL,rating8 int NOT NULL,rating9 int NOT NULL,rating10 int NOT NULL,rating11 int NOT NULL,rating12 int NOT NULL,rating13 int NOT NULL,rating14 int NOT NULL,rating15 int NOT NULL,FOREIGN KEY(property1,property2) REFERENCES mobiles(property1,property2))engine=innodb;
I have added the primary key also now.....but still i dono whether the foreign key is combination of property1 and property2 ?? :(

Expand|Select|Wrap|Line Numbers
  1. desc mobile_rating;
  2. +-----------+--------------+------+-----+---------+----------------+
  3. | Field     | Type         | Null | Key | Default | Extra          |
  4. +-----------+--------------+------+-----+---------+----------------+
  5. | id        | int(11)      | NO   | PRI | NULL    | auto_increment | 
  6. | property1 | varchar(100) | NO   | MUL | NULL    |                | 
  7. | property2 | varchar(100) | NO   |     | NULL    |                | 
  8. | rating1   | int(11)      | NO   |     | NULL    |                | 
  9. | rating2   | int(11)      | NO   |     | NULL    |                | 
  10. | rating3   | int(11)      | NO   |     | NULL    |                | 
  11. | rating4   | int(11)      | NO   |     | NULL    |                | 
  12. | rating5   | int(11)      | NO   |     | NULL    |                | 
  13. | rating6   | int(11)      | NO   |     | NULL    |                | 
  14. | rating7   | int(11)      | NO   |     | NULL    |                | 
  15. | rating8   | int(11)      | NO   |     | NULL    |                | 
  16. | rating9   | int(11)      | NO   |     | NULL    |                | 
  17. | rating10  | int(11)      | NO   |     | NULL    |                | 
  18. | rating11  | int(11)      | NO   |     | NULL    |                | 
  19. | rating12  | int(11)      | NO   |     | NULL    |                | 
  20. | rating13  | int(11)      | NO   |     | NULL    |                | 
  21. | rating14  | int(11)      | NO   |     | NULL    |                | 
  22. | rating15  | int(11)      | NO   |     | NULL    |                | 
  23. +-----------+--------------+------+-----+---------+----------------+
  24. 18 rows in set (0.00 sec)
Oct 26 '09 #3
Atli
5,058 Expert 4TB
Joint Primary Keys and Foreign Keys are not the same.
If you want both property fields to be Foreign Keys and reference their respective counterpart in the other table, as well as be joined as a Primary Key, you should define each of those three things separately.

More like:
Expand|Select|Wrap|Line Numbers
  1. create table mobile_rating(
  2.     property1 varchar(100) NOT NULL,
  3.     property2 varchar(100) NOT NULL,
  4.     rating1 int NOT NULL,
  5.     rating2 int NOT NULL,
  6.     /* etc... */
  7.     PRIMARY KEY(property1, property2),
  8.     FOREIGN KEY(property1) 
  9.         REFERENCES mobiles(property1),
  10.     FOREIGN KEY(property2) 
  11.         REFERENCES mobiles(property2)
  12. )engine=innodb;

Also, on a different subject...
Your habit of listing a number of identical columns, each representing a separate piece of identical data (rating1, rating2, rating3, etc...) is a very bad design practice.
(See Database Normalization and Table Structures, specifically "1NF")

When you have need to store a number of identical pieces of data for the same row, you should extract them into a new table and link to it.

Say you have this `item` table:
Expand|Select|Wrap|Line Numbers
  1. mysql> SELECT * FROM `item`;
  2. +----+----------+
  3. | id | whatever |
  4. +----+----------+
  5. |  1 | First    |
  6. |  2 | Second   |
  7. |  3 | Third    |
  8. +----+----------+
Rather the creating a `item_rating` table like so:
Expand|Select|Wrap|Line Numbers
  1. mysql> SELECT * FROM `item_rating`;
  2. +----+------------+----------+----------+----------+----------+
  3. | id | item_id_fk | rating_1 | rating_2 | rating_3 | rating_N |
  4. +----+------------+----------+----------+----------+----------+
  5. |  1 |          1 |        3 |        4 |        3 |        5 |
  6. |  2 |          2 |        2 |        2 |        5 |        3 |
  7. |  3 |          3 |        3 |        5 |        1 |        1 |
  8. +----+------------+----------+----------+----------+----------+
It is better to do:
Expand|Select|Wrap|Line Numbers
  1. mysql> SELECT * FROM `item_rating`;
  2. +----+------------+--------+
  3. | id | item_id_fk | rating |
  4. +----+------------+--------+
  5. |  1 |          1 |      3 |
  6. |  2 |          1 |      4 |
  7. |  3 |          1 |      3 |
  8. |  4 |          1 |      5 |
  9. |  5 |          2 |      2 |
  10. |  6 |          2 |      2 |
  11. |  7 |          2 |      5 |
  12. |  8 |          2 |      3 |
  13. |  9 |          3 |      3 |
  14. | 10 |          3 |      5 |
  15. | 11 |          3 |      1 |
  16. | 12 |          3 |      1 |
  17. +----+------------+--------+
This way you don't limit yourself to a fixed number of ratings per item.
Oct 27 '09 #4
pradeepjain
563 512MB
okie i will have a look at it !! i have a small doubt in the code u showed



Expand|Select|Wrap|Line Numbers
  1. 1. create table mobile_rating(
  2.    2.     property1 varchar(100) NOT NULL,
  3.    3.     property2 varchar(100) NOT NULL,
  4.    4.     rating1 int NOT NULL,
  5.    5.     rating2 int NOT NULL,
  6.    6.     /* etc... */
  7.    7.     PRIMARY KEY(property1, property2),
  8.    8.     FOREIGN KEY(property1) 
  9.    9.         REFERENCES mobiles(property1),
  10.   10.     FOREIGN KEY(property2) 
  11.   11.         REFERENCES mobiles(property2)
  12.   12. )engine=innodb;
  13.  

is the primary key for this table mobile_rating needs to be PRIMARY KEY(property1, property2), it can be just an auto_increment value rite !then also the foreign keys will work i guess!
Oct 28 '09 #5
Atli
5,058 Expert 4TB
In the code I posted, the two foreign keys are joined as the primary key.
This means that no two rows in the parent table can be linked together twice.

If you use an auto_increment column as the PK in this table, and leave the two foreign keys by themselves, that restriction does not apply, and you could be listing the same two rows multiple times.

There is another way to handle this entirly, which would probably work better in most respects.

It is usually not advisable to use a VarChar field as the PK in a table, much less a join PK made up of multiple VarChar fields. 99% of the time you are going to want to use a integer as a PK, so that it works sort of like a row index. And you generally want to stay away from join PKs as much as possible, because they can make simple situations overly complex. (Like in your case.)

Consider this scenario:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE `mobile`(
  2.     `id` Int Unsigned Auto_Increment Not Null,
  3.     `property1` VarChar(255) Not Null,
  4.     `property2` VarChar(255) Not Null,
  5.     /* etc ... */
  6.     Primary Key (`id`),
  7.     Unique (`property1`, `property2`)
  8. )ENGINE=InnoDB;
  9.  
  10. CREATE TABLE `mobile_rating`(
  11.     `id` Int Unsigned Auto_Increment Not Null,
  12.     `mobile_id_fk` Int Unsigned,
  13.     `rating` Int Not Null,
  14.     Primary Key (`id`),
  15.     Foreign Key (`mobile_id_fk`)
  16.         References `mobile`(`id`)
  17. )ENGINE=InnoDB;
There the `mobile` table is still constrained by the two property fields (who are joined as a Unique Key), but it has a Integer PK that can be easily referenced by other tables, without it having to worry about the joint PK.

This method also has a few other advantages, like:
  • It should make your queries faster, as comparing integers is faster than comparing strings.
  • It uses less HDD space, because you won't have to duplicate the text fields over and over again in each row in the ratings table. A single integer takes much less space.
  • It will save server resources, as queries for ratings will be shorter without the text keys, and the comparison itself will be less demanding on both the memory and the CPU.
Oct 28 '09 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Tetsuji Ueda | last post by:
Hello, I'm currently porting an application using some other DB to MySQL. The database in question has several tables with foreign key relations. The application is written such that, on table...
6
by: jacob nikom | last post by:
I would like to create data model for a group of stores. All stores in this group are very similar to each other, so it is natural to allocate one MySQL database per store. Each database is going...
0
by: Ed Smith | last post by:
I have two questions about REFERENCES: 1. It appears that mySQL treats REFERENCES associated with an attribute differently than FOREIGN KEY (<blah>) REFERENCES... Specifically, the first form...
0
by: Phil | last post by:
I am using MySQL 4.1.1-1. When I add a "named" foreign key constraint alter table sb_query_nm_sub_tp add constraint f1sbquerynmsubtp foreign key (query_nm) references sb_query_class...
0
by: Mike Chirico | last post by:
Interesting Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Copyright (GPU Free Documentation License) 2004 Last Updated: Mon Jun 7 10:37:28 EDT 2004 The latest...
2
by: Jeff Silverman | last post by:
I am working on my first database and I think I want to build a table with a foreign key in it. The O'Reilly book on MySQL says that MySQL does not support foreign keys, but it still talks about...
4
by: Simon Bond | last post by:
Hi there, I should point out I am very new to MySql, im trying to learn what I can. Basically im using Navicat to work with my databases. When making a foreign key, i am asked to enter the...
4
by: Ted | last post by:
Understand, I have developed a number of applications using RDBMS, including MySQL, PostgreSQL and MS Access, but this is my first experience with MS SQL. I'd bet my bottom dollar that MS SQL...
1
by: somaskarthic | last post by:
Hi I'm using MySQL 5.0.18 . Here i'm unable to assign foreign key. Though it is assigned , while adding a row to a child table , it violates the foreign key rule. Database engine is MyISAM. It is...
6
Atli
by: Atli | last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users. Anyone should be able to get...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.