Foreign Key Constraint in MS SQL Server 2008

“Foreign key constraint” is one of the most useful database features. Every developer should know about this if they need to link data from one table to another. It is useful if you are establishing relationships between tables.

For example, suppose you have two tables in your database, named MovieDatabase and MovieStocks, which are providing information to your ASP.NET website.

The MovieDatabase table is used by the administrator as the central database. It lists all information pertaining to the movies they sell and shows that information on their website.

The MovieStocks table is used by the sales and inventory department of the website. It monitors how much stock is available for their potential customers.

These two tables have the following fields and specifications:

MovieDatabase:

  • MovieID – Int and a Primary Key Index
  • Movietitle – nvarchar(50)
  • Moviedirector – nvarchar(50)
  • MovieGenre – nvarchar (25)

MovieStocks:

  • MovieStockID – Int and a Primary Key Index
  • Movietitle – nvarchar(50)
  • MovieDVDstockqty – Int
  • MovieTotalSales – Int

If a certain ASP.NET web application is accessing/updating the MovieDatabase table and editing the title of the movie (Movietitle field), what will happen?

You will have two tables with “inconsistent” data, because a certain row is updated such that the Movietitle in the MovieDatabase table is updated to read “The Iron Man” instead of “Iron Man” — but in the MovieStocks table, the title will be left unchanged (it is still “Iron Man”).

This data discrepancy can be confusing to manage in the long run, and affects the accuracy of the information delivered by the dynamic website. This can affect your business if you are managing fairly large database tables.

You might be tempted to manually edit the MovieStocks table after editing the MovieDatabase. This might sound okay if the table is very small. But manual editing is impossible if you are dealing with thousands of rows in a table.

The most effective solution is to use “foreign key constraint.” If the movie title is changed to “The Iron Man” in the MovieDatabase table, then the movie title “Iron Man” in the MovieStocks table will also be automatically updated to “The Iron Man” because these two tables are “linked” together using foreign key constraint. In this case, this feature helps to increase the database management efficiency by avoiding any manual editing of large database tables.

{mospagebreak title=Foreign Key Constraint in MS SQL Server}

Now that you know the importance of foreign key constraint, let’s go more deeply into the theory and its implementation in the SQL Server 2008 environment using Visual Web Developer Express 2008.

To easily understand the SQL Server 2008 properties of foreign key constraint, let’s use the example illustrated previously. Refer to the screen shot below:

You will find four yellow boxes. These are the properties that you will need to define when creating a foreign key constraint in SQL Server 2008. This procedure will be discussed in the last section of this tutorial.

The following are the definitions of those four properties:

Primary/Unique Key Base Table. This table contains the source data that will be linked to other tables (also known as “Foreign key base table”).

In the previous example, the primary key base table is the MovieDatabase table.

Primary/Unique Key Column. This column contains the unique data that is also available in other tables. By “linking” two tables, any update of this column will also be reflected in the foreign key base table.

You will need to specify what the “unique” key column is in the primary key base table after its database table creation and field type’s configuration. Assigning a “unique” key index column to the primary key base table is required when  implementing foreign key constraint.

To declare a column a “unique key column or index” type, you need to ensure that the field data are unique.

Example: cats, dogs, bears, wolf

But any duplication/repetition in the column data will not qualify a column to be a “unique key index” example: cats, dogs, bears, cats

Foreign Key Base Table. This is the target table or dependent of the primary key table. If the primary key table is updated, this table will be updated also by using the foreign key constraint technique. Foreign and Primary key tables should belong to one database in the same server for a simpler method of doing foreign key constraint.

Foreign Key Columns. By establishing the relationship between the foreign and primary key table, foreign columns data should be exactly the same as primary/unique key column data at all times. Also, the primary and foreign key columns’ data field type should be the same.

{mospagebreak title=Creating foreign key constraint in Visual Web Developer}

To implement foreign key constraint in SQL Server, let’s illustrate it using an actual web application project. It is required that you read this tutorial and then recreate the project from it in your own local host environment using Visual Web Developer Express (2008). This includes the Default.aspx and the worktodo.mdf database.

Once this is done, try viewing the project’s output in a web browser by going to File -> View in Browser. Try changing all of the Complete statuses to Ongoing under the “Status” column by clicking the “Edit” link, and then changing the value using a drop down list to Ongoing. After setting it, click “Update.”

Now try clicking the “Edit” link again, and change the status of any task to Complete. You will be surprised to no longer see “Complete” as one of the drop down list options. Since the drop down list control is blind to the Status column in the tasklists database table, it returns all unique entries in the status column; that column is now completely set to Ongoing. Because of this, you will not be able to select “Complete” as a Status.

You should have the following output like shown below:

To solve this problem, you need to use foreign key constraint by following the complete procedure below:

Step 1. Create a new table in the worktodo database with the following specifications:

  • Field name1: StatusID
  • Data type: int
  • Allow nulls: no
  • Primary key index: Yes
  • Field name2: Status
  • Data type: nvarchar(10)
  • Allow nulls: no
  • Name of this new table: StatusTable

Refer to the procedure for creating tables for details.

Step 2. In the database explorer, right click on “StatusTable,” and then click “Show Table Data.” Add the following data under the “Status” column

a. Ongoing

b. Complete

The table definition and added data should look like this:

Bear in mind that the Status column in StatusTable should have exactly the same field type as the Status column in the tasklists table.

Step 3: You need to set the field “Status” in the StatusTable as a unique key column. To do this, follow the sub-steps below:

a. Go to Table Designer -> Indexes/Keys -> Click “Add.” 

b. Under (General) -> Columns, change the default value it to “Status” and sort order to “Descending.”

c. Under (General) -> Type, change it to “Unique Key.”

d. Click Close and Save.

Step 4: Close all StatusTable definitions and table.

Step 5: Right on the tasklist table in Database Explorer, then “Open Table Definition.”

Step 6: Go to Table Designer -> Relationships. This is where you need to define your Foreign Key Constraint properties discussed previously.

{mospagebreak title=Creating foreign key constraint in Visual Web Developer, continued}

Step 7: Click “Add,” then expand “Tables and Columns Specifications.”

Step 8: Click the Ellipsis (…) besides “Tables and Columns Specifications” to show the properties.

Step 9: You will need to define four properties. For this application, they will be:

a. Primary Key table: StatusTable

b. Foreign Key table: tasklists

c. Unique Key index (below Primary key table) -> Status

d. Foreign key index (below Foreign key table) -> Status

It should look like the screen shot below:

Step 10. Click “Close” and save your work. If a Warning displays “…This following will be saved to your database …?” just click Yes.

Step 11. The foreign key constraint configuration has been completed.

Step 12. You should change the database table source for the Status column in Default.aspx from tasklists to Statustable. To do this, go to the Source Code view of Default.aspx and then change (the SqlDataSource web control under Gridview):

<asp:SqlDataSource ID="SqlDataSource2" runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT DISTINCT [Status] FROM [tasklists]">

</asp:SqlDataSource>

TO:

<asp:SqlDataSource ID="SqlDataSource2" runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT DISTINCT [Status] FROM [StatusTable]">

</asp:SqlDataSource>

Step 13. Save your work and test it again in the browser. Even if you change all of the statuses to Ongoing, you can now select “Complete” as an option in the drop down list.

One thought on “Foreign Key Constraint in MS SQL Server 2008

[gp-comments width="770" linklove="off" ]