How to use foreign keys with PHP

Foreign key columns/constraints disambiguation

So I understand how to create foreign keys and I know what is the purpose of the FK. But I have a problem in understanding How to use them.

Assuming you are referring to the foreign key constraints, the short answer would be you just don't use them.

And here comes the long one:

We are accustomed to refer to columns being foreign keys to other tables. Especially during the normalization process, phrases like "user_purchase.i_id is a foreign key to the items table" would be very common. While that's a perfectly valid way to describe the relationship, it can get a little fuzzy when we reach the implementation phase.

Suppose you have created your tables without the FOREIGN KEY clauses:

CREATE TABLE user(  id INT(11) NOT NULL AUTO_INCREMENT,  username VARCHAR(50) NOT NULL,  password VARCHAR(20) NOT NULL,  PRIMARY KEY (id));CREATE TABLE items(  i_id INT(11) NOT NULL AUTO_INCREMENT,  name TINYTEXT NOT NULL,  price DECIMAL(8,2) NOT NULL,  PRIMARY KEY (i_id));CREATE TABLE user_purchase(  i_id INT(11) NOT NULL,  name TINYTEXT NOT NULL,  id INT(11) NOT NULL,);

Notice that, relation-wise, the foreign key columns are still implemented. There's a column that references the user table (id) and another one that references the items table (i_id) -- let's put the name column aside for a moment. Consider the following data:

  user              user_purchase    items| id  username |    | id  i_id |    | i_id  name            price || 23  john     |    | 55   10  |    |  10   chocolate bar    3.42 || 55  mary     |    | 70   10  |    |  33   mobile phone    82.11 || 70  fred     |    | 70   33  |    |  54   toothpaste       8.67 |                    | 55   10  |    |  26   toy car          6.00 |                    | 70   26  |

The relation is there. It is implemented by means of the user_purchase table, which holds information as to who bought what. If we were to query the database for a relevant report, we would do:

select * from user_purchase pjoin user u on (p.id=u.id)join items i on (p.i_id=i.i_id)

And that's how we use the relation and the foreign key columns involved.

Now, what if we do:

insert into user_purchase (id,i_id) values (23,99)

Apparently, this is an invalid entry. Although there is a user with id=23, there's no item with i_id=99. The RDBMS would allow that to happen, because it doesn't know any better. Yet.

That's where foreign key constraints come into play. By specifying FOREIGN KEY (i_id) REFERENCES items(i_id) in the user_purchase table definition, we essentially give the RDBMS a rule to follow: entries with i_id values that are not contained in the items.i_id column are not acceptable. In other words, while a foreign key column implements the reference, a foreign key constraint enforces the referential integrity.

Note, however, that the above select wouldn't change, just because you defined a FK constraint. Thus, you don't use FK constraints, the RDBMS does, in order to protect your data.

Redundancies

...what if I want several columns? Why don't we use several foreign keys for different columns of the same table?

Ask yourself: Why would you want that? If the two foreign keys are to serve the same purpose, the redundancy will eventually get you in trouble. Consider the following data:

 user_purchase                   items| id  i_id  name           |    | i_id  name            price || 55   10   chocolate bar  |    |  10   chocolate bar    3.42 || 70   10   chocolate bar  |    |  33   mobile phone    82.11 || 70   33   mobile phone   |    |  54   toothpaste       8.67 || 55   10   toothpaste     |    |  26   toy car          6.00 || 70   26   toy car        |

What's wrong with this picture? Did user 55 buy two chocolate bars, or a chocolate bar and a toothpaste? This kind of ambiguity can lead to a lot of effort to keep data in-sync, which would be unnecessary if we just kept one of the foreign keys. In fact, why not drop the name column altogether, since it is implied by the relation.

Of course, we could resolve this by implementing a composite foreign key, by setting PRIMARY KEY(i_id,name) for the items table (or defining an extra UNIQUE(i_id,name) index, it doesn't realy matter) and then setting a FOREIGN KEY(i_id,name) REFERENCES items(i_id,name). This way, only (i_id,name) couples that exist in the items table would be valid for user_purchases. Apart from the fact that you would still have one foreign key, this approach is totally unnecessary, provided that the i_id column is already enough to identify an item (can't say the same for the name column...).

However, there's no rule against using multiple foreign keys to a table. In fact, there are circumstances that demand such an approach. Consider a person(id,name) table and a parent(person,father,mother) one, with the following data:

 person             parent| id  name    |    | person  father  mother || 14  John    |    |   21      14      59   || 43  Jane    |    |   14      76      43   || 21  Mike    || 76  Frank   || 59  Mary    |

Obviously, all three columns of the parent table are foreign keys to person. Not for the same relation, though, but for three different ones: Since a person's parents are persons too, the two corresponding columns must reference the same table person does. Note, however, that the three fields not only can but also have to refer different persons in the same parent row, since noone is his own parent and noone's father is his mother as well.

Foreign keys are used in joins. For instance, if you want to know the usernames that purchased a particular item, you would write:

select u.usernamefrom items ijoin user_purchase up on i.i_id = up.i_idjoin user u on u.id = up.idwhere i.name = "Some product name"

They may also be used by the database engine itself. It can detect if you create a row in user_purchase whose id or i_id column doesn't match anything in the referenced column in the other table.

You should not replicate the name column in the user_purchase table. The name is just an attribute of the item, it's not specific to any particular purchase. If you need to get the name of the item that was purchased, join with the items table.

Instead of reading so many links, just try to implement this in any simple project. I'm just explaining how we gonna use the above tables.

Suppose you 3 users in user table and 5 items in items table.

user table

id  |  username |  password  1      abc         123 2      def         456 3      qwe         987 

items table

i_id  |  name   |  price  1      item 1    6 2      item 2    8 3      item 3    11  4      item 4    3 5      item 5    14  

your user_purchase table look like this

CREATE TABLE user_purchase( i_id INT(11) NOT NULL, id INT(11) NOT NULL, FOREIGN KEY (i_id) REFERENCES items(i_id), FOREIGN KEY (id) REFERENCES user(id));

There is no need of item name again in this table. So I have removed.

i_id |  id   1      1       1      2 2      2       3      3      

In the above table we will get, user 1 has purchased item 1, user 2 has purchased item 1,item 2 and user 3 has purchased item 3.

This is how normalization works. You can use MySQL JOIN for getting user name and item details

SELECT B.user_name,C.name AS item_name,C.priceFROM user_purchase A JOIN user B ON A.id = B.idJOIN items C ON A.i_id = C.i_id

Here is foreign key use to join

A.id = B.idA.i_id = C.i_id