mysql foreign key

pradeepjain

i have 1st table created like this

Expand|Select|Wrap|Line Numbers

 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

 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

 
+------------+----------------------------------------------------------------------------------------+------+-----+---------+-------+

| Field      | Type                                                                                   | Null | Key | Default | Extra |

+------------+----------------------------------------------------------------------------------------+------+-----+---------+-------+

| property1  | varchar(100)                                                                           | NO   | PRI | NULL    |       | 

| property2  | varchar(100)                                                                           | NO   | PRI | NULL    |       | 

| property3  | enum('Basic','Smart','Camera','Music','PDA')                                           | YES  |     | NULL    |       | 

| property4  | enum('2000-5000','5000-10000','10000-15000','15000-20000','20000-25000','25000-30000') | YES  |     | NULL    |       | 

| property5  | enum('CDMA','GSM','BOTH')                                                              | YES  |     | NULL    |       | 

| property6  | enum('Android','Symbian','Linux','Windows''Blackberry')                                | YES  |     | NULL    |       | 

| property7  | enum('Block','Slide','Clamshell','Sideflip')                                           | YES  |     | NULL    |       | 

| property8  | varchar(100)                                                                           | NO   |     | NULL    |       | 

| property9  | varchar(100)                                                                           | NO   |     | NULL    |       | 

| property10 | varchar(100)                                                                           | NO   |     | NULL    |       | 

| property11 | varchar(100)                                                                           | NO   |     | NULL    |       | 

| property12 | varchar(100)                                                                           | NO   |     | NULL    |       | 

| property13 | varchar(100)                                                                           | NO   |     | NULL    |       | 

| property14 | varchar(100)                                                                           | NO   |     | NULL    |       | 

| property15 | varchar(100)                                                                           | NO   |     | NULL    |       | 

+------------+----------------------------------------------------------------------------------------+------+-----+---------+-------+

but the second table looks like i.e mobile_rating

Expand|Select|Wrap|Line Numbers

  
+-----------+--------------+------+-----+---------+-------+

| Field     | Type         | Null | Key | Default | Extra |

+-----------+--------------+------+-----+---------+-------+

| property1 | varchar(100) | NO   | MUL | NULL    |       | 

| property2 | varchar(100) | NO   |     | NULL    |       | 

| rating1   | int(11)      | NO   |     | NULL    |       | 

| rating2   | int(11)      | NO   |     | NULL    |       | 

| rating3   | int(11)      | NO   |     | NULL    |       | 

| rating4   | int(11)      | NO   |     | NULL    |       | 

| rating5   | int(11)      | NO   |     | NULL    |       | 

| rating6   | int(11)      | NO   |     | NULL    |       | 

| rating7   | int(11)      | NO   |     | NULL    |       | 

| rating8   | int(11)      | NO   |     | NULL    |       | 

| rating9   | int(11)      | NO   |     | NULL    |       | 

| rating10  | int(11)      | NO   |     | NULL    |       | 

| rating11  | int(11)      | NO   |     | NULL    |       | 

| rating12  | int(11)      | NO   |     | NULL    |       | 

| rating13  | int(11)      | NO   |     | NULL    |       | 

| rating14  | int(11)      | NO   |     | NULL    |       | 

| rating15  | int(11)      | NO   |     | NULL    |       | 

+-----------+--------------+------+-----+---------+-------+

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

 
property1 | varchar(100) | NO   | MUL | NULL    |       | 

| 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!!?

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.

pradeepjain

Expand|Select|Wrap|Line Numbers

  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

 desc mobile_rating;

+-----------+--------------+------+-----+---------+----------------+

| Field     | Type         | Null | Key | Default | Extra          |

+-----------+--------------+------+-----+---------+----------------+

| id        | int(11)      | NO   | PRI | NULL    | auto_increment | 

| property1 | varchar(100) | NO   | MUL | NULL    |                | 

| property2 | varchar(100) | NO   |     | NULL    |                | 

| rating1   | int(11)      | NO   |     | NULL    |                | 

| rating2   | int(11)      | NO   |     | NULL    |                | 

| rating3   | int(11)      | NO   |     | NULL    |                | 

| rating4   | int(11)      | NO   |     | NULL    |                | 

| rating5   | int(11)      | NO   |     | NULL    |                | 

| rating6   | int(11)      | NO   |     | NULL    |                | 

| rating7   | int(11)      | NO   |     | NULL    |                | 

| rating8   | int(11)      | NO   |     | NULL    |                | 

| rating9   | int(11)      | NO   |     | NULL    |                | 

| rating10  | int(11)      | NO   |     | NULL    |                | 

| rating11  | int(11)      | NO   |     | NULL    |                | 

| rating12  | int(11)      | NO   |     | NULL    |                | 

| rating13  | int(11)      | NO   |     | NULL    |                | 

| rating14  | int(11)      | NO   |     | NULL    |                | 

| rating15  | int(11)      | NO   |     | NULL    |                | 

+-----------+--------------+------+-----+---------+----------------+

18 rows in set (0.00 sec)

Atli

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

 create table mobile_rating(

    property1 varchar(100) NOT NULL,

    property2 varchar(100) NOT NULL,

    rating1 int NOT NULL,

    rating2 int NOT NULL,

    /* etc... */

    PRIMARY KEY(property1, property2),

    FOREIGN KEY(property1) 

        REFERENCES mobiles(property1),

    FOREIGN KEY(property2) 

        REFERENCES mobiles(property2)

)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

 mysql> SELECT * FROM `item`;

+----+----------+

| id | whatever |

+----+----------+

|  1 | First    |

|  2 | Second   |

|  3 | Third    |

+----+----------+

Rather the creating a `item_rating` table like so:

Expand|Select|Wrap|Line Numbers

 mysql> SELECT * FROM `item_rating`;

+----+------------+----------+----------+----------+----------+

| id | item_id_fk | rating_1 | rating_2 | rating_3 | rating_N |

+----+------------+----------+----------+----------+----------+

|  1 |          1 |        3 |        4 |        3 |        5 |

|  2 |          2 |        2 |        2 |        5 |        3 |

|  3 |          3 |        3 |        5 |        1 |        1 |

+----+------------+----------+----------+----------+----------+

It is better to do:

Expand|Select|Wrap|Line Numbers

 mysql> SELECT * FROM `item_rating`;

+----+------------+--------+

| id | item_id_fk | rating |

+----+------------+--------+

|  1 |          1 |      3 |

|  2 |          1 |      4 |

|  3 |          1 |      3 |

|  4 |          1 |      5 |

|  5 |          2 |      2 |

|  6 |          2 |      2 |

|  7 |          2 |      5 |

|  8 |          2 |      3 |

|  9 |          3 |      3 |

| 10 |          3 |      5 |

| 11 |          3 |      1 |

| 12 |          3 |      1 |

+----+------------+--------+

This way you don't limit yourself to a fixed number of ratings per item.

pradeepjain

okie i will have a look at it !! i have a small doubt in the code u showed

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;

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!

Atli

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

 
CREATE TABLE `mobile`(

    `id` Int Unsigned Auto_Increment Not Null,

    `property1` VarChar(255) Not Null,

    `property2` VarChar(255) Not Null,

    /* etc ... */

    Primary Key (`id`),

    Unique (`property1`, `property2`)

)ENGINE=InnoDB;
 
CREATE TABLE `mobile_rating`(

    `id` Int Unsigned Auto_Increment Not Null,

    `mobile_id_fk` Int Unsigned,

    `rating` Int Not Null,

    Primary Key (`id`),

    Foreign Key (`mobile_id_fk`)

        References `mobile`(`id`)

)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.

mysql foreign key

re: mysql foreign key

re: mysql foreign key

re: mysql foreign key

re: mysql foreign key

re: mysql foreign key

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/about