Primary and Foreign Keys
Tables are joined through a relational system. It has been displayed that some field in table1 is connected to a field in table2. This is a foreign key since it is used to connect the two tables. The foreign key can be any two fields that will hold the exact same data. It is possible to incorrectly link tables and still obtain a result but the DBA needs to be careful when joining tables so that the result is correct and will not have an error because of a bad association.
PRIMARY KEYs are typically used as a FOREIGN KEY in another table. The PRIMARY KEY is the main way to search a table and is indexed so that the search is done very quickly. FOREIGN KEYs are also indexed which allows the associations to be handled quickly as well. This association is one of four relationships they can be either One-to-One, One-to-Many, Many-to-One, Many-to-Many. This relationship is called a constraint; however, the One-to-Many and Many-to-One are identical in how the database handles the query the difference is how the DBA views the tables since it could go either way. One-to-One is used where a direct relationship is necessary. This is used typically for extending a table by breaking a large amount of data into separate tables for an easier reference and memory management. The One-to-Many relationship is the most common relationship because often there is a primary record that may have dozens of entries on another table. The relationship could be used for inventory, notes, error messages, friend’s lists or anything that would tie a single entry to multiple rows from another table. The Many-to-Many is not often used however it does have applications sometimes an inventory item may be associated to several categories but each category also has multiple inventory items. This means that both could reference each other in multiple ways depending on which direction the DBA is searching the database.