I have a number of databases linked using a primary key. A problem with this is that if an error is made on one database then the information held on the others for that client is thrown out of sequence. I posted a similiar query before and someone replied telling me about foreign keys, i was wondering if anyone can expand on this for me.
Here is a general primer on the topic of foreign keys and relationships.
In a relational database (Access), the data in one table is related to the data in other tables. In general, tables can be related in one of three different ways: one-to-one, one-to-many or many-to-many.
One to One
In a one-to-one relationship each record in one table has at most one related record in another table.
In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table.
One to Many
A one-to-many relationship, often referred to as a "master-detail" or "parent-child" relationship, is the most usual relationship between two tables in a database.
A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.
Many to Many
In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction table) whose primary key consists of two fields — the foreign keys from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table.
A many-to-many relationship means that for each record in one table there can be many records in another table and for each record in the second table there can be many in the first.
Many-to-many relationships can not be directly represented in relational database programs and have to be built by using two or more one-to-many relationships.
You define a relationship by adding the tables that you want to relate to the Relationships window, and then dragging the key field from one table and dropping it on the key field in the other table.
The kind of relationship that Microsoft Access creates depends on how the related fields are defined:
A one-to-many relationship is created if only one of the related fields is a primary key or has a
A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes. A many-to-many relationship is really two one-to-many relationships with a third table whose primary key consists of two fields — the foreign keys from the two other tables.
Note If you drag a field that isn't a primary key and doesn't have a unique index to another field that isn't a primary key and doesn't have a unique index, an indeterminate relationship is created. In queries containing tables with an indeterminate relationship, Microsoft Access displays a default join line between the tables, but referential integrity won't be enforced, and there's no guarantee that records are unique in either table.
When creating a relation between two tables MS Access provides uses the Referential Integrity feature. This feature prevents adding records to a detail table for which there is no matching record in the master table. It will also cause the key fields in the detail table to be changed when the corresponding key fields in the master are changed - this is commonly referred to as a cascading update. The second options is to enable cascading deletes. This causes the deletion of all the related records in a detail table when the corresponding record in the master table gets deleted.
Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!
Or try our Ask An Expert service to answer any of your questions!