Foreign Key Constraint in MS SQL Server 2008
(Page 1 of 4 )
“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.
Next: Foreign Key Constraint in MS SQL Server >>
More MS SQL Server Articles
More By Codex-M
