What exactly is a foreign key?

问题:

Ok. So I know what a primary key in DB is. If you have a table in a database, a primary key is a single value that is unique to each row in your table. For example:

id   | name    | whatever
-------------------------
1      Alice     ....
2      Bob       ....
45     Eve       ....
988    ....      ....

So I need a good, simple example to explain what exactly a foreign key is. Because I just don't get it :)

---

Edit: OK it's pretty easy, I guess I was over-complicating the problem.

So one final question, the only restriction on foreign keys is that it they are a valid primary key value in the table I am referring to?

回答1:

A foreign key is a field that points to a primary key of another table.

Example:

Table Name - Users

UserID    UserName    UserRoleID
1         JohnD       1
2         CourtneyC   1
3         Benjamin    2

Table Name - UserRoles

UserRoleID    Desc
1             Admin
2             Moderator

You can see that Users.UserRoleID is a foreign key which points to the primary key UserRoles.UserRoleID

The use of foreign keys makes setting up relationships on other tables simple, allowing you to link together the data of multiple tables in a nice way:

Example:

SELECT
    a.UserID, 
    a.UserName, 
    b.Desc as [UserRole]
FROM 
    Users a INNER JOIN 
        UserRoles b ON a.UserRoleID = b.UserRoleID

Output would then be:

UserID    UserName    User Role
1         JohnD       Admin
2         CourneyC    Admin
3         Benjamin    Moderator

回答2:

Let's say you have another field, which is the home city:

id   | name    | city
-------------------------
1      Alice     San Francisco
2      Bob       New York
45     Eve       New York
988    Bill      San Francisco

Now, it does not make sense to repeat the same cities in many rows. This could lead you to typos, excessive space usage, difficulties to bring up results among other problems. So you use a foreign key:

id   | name    | fk_city
-------------------------
1      Alice     1
2      Bob       2
45     Eve       2
988    Bill      1

home city table:

id   | name
-------------------------
1    | San Francisco
2    | New York

Hope it makes things clearer for you. :-)

Update: about your final question: Yes. :-)

回答3:

A foreign key is a column in one table that should uniquely identify something in another table. Thus, the values should correspond to primary keys in that other table.

For example, if you have a table of students taking courses, every record would include a student id and a course id. These are foreign keys into a student table (where there is one record for each student id), and a courses table (where there is one record for each course id).

Referential integrity means that all your foreign keys actually correspond to primary keys in these target tables. For example, all the student ids and course ids in your registration table correspond to real student ids and course ids.

回答4:

id   | name    | whatever | countryid
-------------------------------------
1      Alice     ....       13
2      Bob       ....       42
45     Eve       ....       1
988    ....      ....       2

id   | countryid
----------------
1      Japan
2      Spain
13     Norway
42     Italy

The foreign key points from the person table (first) to a row in the country table (second)

回答5:

In a relational database a one-to-many relationship is implemented by having the child table reference the ID of the parent table. The parent ID in the Child table is called a Foreign Key as it references a primary key of another table.

回答6:

A foreign key is a field that references another table in the database. For example, suppose you had 2 tables, PERSON and ADDRESS. There is a field in PERSON called ID and a field in ADDRESS called PERSON_ID. You would make PERSON_ID refer to PERSON.ID as a foreign key. What this means is that you can't have an address that is not connected to a person, since the value in the ADDRESS.PERSON_ID field must exist in the table PERSON.

回答7:

using your table example, assume you have another table:

cartid  |  id  | itemid
-----------------------
100        1       abc
101        1       cde

in this table, the primary key is the cartid, the foreign key is the id, which would be linked to your first table. user 1 has two carts, each cart having one item each.

a foreign key is the what you use to link two or more tables that have related information to each other.

回答8:

A foreign key is the primary key from another table stored on your table. Say you have a table of customers and a table of orders. The CustomerId is likely the primary key on the customer table, and the OrderId is likely the primary key on the order table. But on the order table you need to know the customer for this order, no? Therefore you need to store the CustomerId on the order table. In this case the CustomerId on the order table is a foreign key.

I would point out that there is no requirement that a primary key (and therefore a foreign key) be a single column. It's simpler, sure. But I've worked on enterprise systems where the primary key was 11 columns long, and I'm sure there are examples longer than that. That is, you needed to know the value for 11 different columns before you can uniquely identify the row.