Database Essentials - Invasion Of The Foreign Keys
(Page 4 of 9 )
You will notice that it is the CustomerID field, which appears in all three of the tables, that makes it possible to link different tables together. In such a case, the CustomerID field is also referred to as a "foreign key", since it is used to refer to records in different tables. In the example above, the name of the foreign key field is the same for all tables; this is not necessary at all times.
When each and every foreign key value is related to a field in another table, this relationship being unique, the system is said to be in a state of "referential integrity". In other words, if the CustomerID field is present in all the tables once and only once, and if a change to the CustomerID field in any single table is reflected in all other tables, referential integrity is said to exist.
This concept of referential integrity is a basic one, and one that becomes very important when designing a database with more than one table. When foreign keys are used to link one table to another, referential integrity, by its very nature, imposes constraints on inserting new records and updating existing records. For example, if a table only accepts certain types of values for a particular field, and other tables use that field as their foreign key, this automatically imposes certain constraints on the dependent tables. Similarly, referential integrity demands that a change in the field used as a foreign key - a deletion or new insertion - must immediately be reflected in all dependent tables.
Many of today's databases take care of this automatically - if you've worked with Microsoft Access, for example, you'll have seen this in action - but some don't. In the case of the latter, the task of maintaining referential integrity falls to the application developer, by manually updating all dependent tables to reflect changes in foreign key values.
Next: Looking Up The Index >>
More Administration Articles
More By icarus, (c) Melonfire
/ 7 
