Database Foreign Keys and Relational Concepts

A foreign key comprises one or more columns in a table whose values match the values in the primary key or unique key of another table. Defining the foreign key causes SQL Server 2012 to enforce referential integrity automatically. The image below provides an example of a foreign key in one table that relates to the primary key in another.

Let us look first at the column in the Employees table called "emp_state_id" listing the state in which the employee lives. This column refers to a list of possible values contained in the States table. The States table contains a state_id column that uniquely refers to the name of the state in a column called "state_name." Because the state_id column uniquely identifies a row in the States table, state_id is the primary key for that table. You want to prevent values from the emp_state_id column of the Employees table that are not also values in the state_id column in the States table from being updated. Therefore, you define the emp_state_id column in the Employees table as being the foreign key that relates to the primary key in the States table. Null values apply to all tables, regardless of how data integrity is ensured. In the next lesson, you will learn all about them.

Foreign keys are both a method of ensuring data integrity and a manifestation of the relationships between tables. When you add a foreign key to a table, you are creating a dependency between the table for which you defi ne the foreign key (the referencing table) and the table your foreign key references (the referenced table). After adding a foreign key, any record you insert into the referencing table must have a matching record in the referenced column(s) of the referenced table, or the value of the foreign key column(s) must be set to NULL. This can be a little confusing, so let us take a look at an example.
Note: When I say that a value must be "set to NULL", I am referring to how the actual INSERT statement looks. As you will learn in a moment, the data may actually look slightly diff erent once it gets in the table, depending on what options you have set in your FOREIGN KEY declaration.
For this example, create another table in the Accounting database called Orders.
One thing you will notice in this CREATE script is that you are going to use both a primary key and a foreign key. A primary key, as you will see as you continue through the design, is a critical part of a table. Your foreign key is added to the script in almost exactly the same way as your primary key was, except that you must say what you are referencing. The syntax goes on the column or columns that you are placing your FOREIGN KEY constraint on, and looks something like this:

<column name> <data type> <nullability>
FOREIGN KEY REFERENCES <table name>(<column name>)
[ON DELETE {CASCADE|NO ACTION|SET NULL|SET DEFAULT}]
[ON UPDATE {CASCADE|NO ACTION|SET NULL|SET DEFAULT}]

Before moving on to the next lesson, click the exercise link below for some hands-on practice with primary and foreign keys.
Defining Primary Foreign Keys - Exercise