How the foreign key works

Mysql 15: Foreign key

Written by: maffelu , 2009-04-16 21:23:01

Foreign keys links a table to another table by setting a shared value in a field. In this example, we'll use the members-table from the Primary key-example and create another table that handle member orders. We want each order ID to be unique, so that will require one Primary key, but we will also need a link to the members table from the orders table we are about to create, so we'll use the foreign key concept.

Syntax:

CREATE TABLE table_name
(
column_1 datatype,
column_2 datatype,
linkcolumn datatype,
PRIMARY KEY (column_1),
FOREIGN KEY (linkcolumn) REFERENCES otherTable(otherTablePrimaryKeyField)
)

Example:

CREATE TABLE memberorders

(

o_ID int NOT NULL AUTO_INCREMENT,

o_Number int NOT NULL,

o_Name varchar(255) NOT NULL,

p_ID int,

PRIMARY KEY (o_ID),

FOREIGN KEY (p_ID) REFERENCES members(m_ID)

)

Now we have two tables, members(from previous article, see Primary key, and memberOrders. If we fill them up a bit and take a look at them, things might get clearer:

members

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

|m_ID                |username            |password            |email               |

+____________________+____________________+____________________+____________________+

|1                    Billy                mysecret             hatemail@hotmail.com|

|2                    Goran                abcdefg              SexyButLonely@ho... |

|3                    Nitro                Balubaluba           GetDown@manlymai... |

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

memberorders

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

|o_ID                |o_Number            |o_Name              |p_ID                |

+____________________+____________________+____________________+____________________+

|1                    295                  Stylish Guestbook    2                   |

|2                    156                  Book of Doom         1                   |

|3                    156                  Slugfest: Ultimatum  1                   |

|4                    468                  Underneath the r...  2                   |

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

What we can see here is that the memberorders table is connected to the members table by the p_ID field. Member #3, Nitro, hasnt bought anything yet, but member #1, Billy, has bought both the Book of Doom and Slugfest: Utlimatum. Btw, this might be a great time to use alias on the memberorders output:

SELECT 

o_ID as 'Order ID', 

o_Number as 'Article Number', 

o_Name as 'Member name', 

p_ID as 'Member ID' 

FROM memberorders

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

|Order ID            |Article Number      |Member name         |Member ID           |

+____________________+____________________+____________________+____________________+

|1                    295                  Stylish Guestbook    2                   |

|2                    156                  Book of Doom         1                   |

|3                    156                  Slugfest: Ultimatum  1                   |

|4                    468                  Underneath the r...  2                   |

|5                    783                  The Little Book ...  3                   |

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

But how do you enter data into a table with a foreign key? Good thing member #3, Nitro, decided to buy The Little Book Of Chaos, article #783. We'll enter that into the table:

INSERT INTO memberorders

(o_Number, o_Name, p_ID)

VALUES

(783, 'The Little Book Of Chaos', 3)

And now it looks like this:

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

|o_ID                |o_Number            |o_Name              |p_ID                |

+____________________+____________________+____________________+____________________+

|1                    295                  Stylish Guestbook    2                   |

|2                    156                  Book of Doom         1                   |

|3                    156                  Slugfest: Ultimatum  1                   |

|4                    468                  Underneath the r...  2                   |

|5                    783                  The Little Book ...  3                   |

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

There are no comments on this article.

Leave a message!

If you have any question or just want to leave a message, just fill out the form below!

Your e-mail will not be visible in your post, it is for validation reasons only

Name:
E-mail:
Comment: