MorkaLork Development

Interesting stuff I've picked up over the years...

Mysql 15: Foreign key

2009-04-16 21:23:01 | 298 views | sql mysql table foreign key id primary key

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 |
+-----------------------------------------------------------------------------------+



Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.